How to find 'MAIN' master record SQL

Question

I'm running a query:

    SELECT
        parent_company_component_id
        ,company_component_id
        ,name
        ,valid_cpy_compnt_type_cs_name
    FROM dbo.cs_company_component
    WHERE company_component_id IN (10217,7726,3109)

Which returns the following results:

I just so happen to know these IDs and am using them for this example.

How can I return results like this across the board grouping together linked companies, until I get to the 'MAIN' company.

There are going to be several other companies where I don't know the IDs I want to be able to basically group these results together so I can keep getting the MAIN company return with it's parent child association.

Answer 1

I've just managed to get my CTE working

WITH CTE AS
(
        SELECT
        parent_company_component_id
        ,company_component_id
        ,name
        ,valid_cpy_compnt_type_cs_name
        ,1 AS level
    FROM dbo.cs_company_component
    WHERE parent_company_component_id IS NULL
    --AND valid_cpy_compnt_type_cs_name = 'MAIN'

    UNION ALL

    SELECT X.parent_company_component_ID, X.company_component_id, x.name, x.valid_cpy_compnt_type_cs_name, CTE.level+1 as Level
   FROM CTE
   JOIN cs_company_component AS X ON X.parent_company_component_id = CTE.company_component_id --AND X.valid_cpy_compnt_type_cs_name = 'MAIN'
)
SELECT * FROM CTE
ORDER BY Level ASC