How to find 'MAIN' master record SQL-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

You can use a recursive CTE to traverse through the relationships.

And it allows you to include extra's like a base id and a level counter.

WITH RCTE_COMP_COMP AS
(
  -- The seed query to start the recursion
  SELECT 
   t.company_component_id AS base_id
 , t.name AS base_name
 , 1 AS lvl
 , t.parent_company_component_id
 , t.company_component_id
 , t.name
 , t.valid_cpy_compnt_type_cs_name
 FROM dbo.cs_company_component t
 WHERE t.parent_company_component_id IS NULL

 UNION ALL
 
 -- The query where the CTE uses itself
 SELECT
   cte.base_id
 , cte.base_name
 , cte.lvl   1
 , t.parent_company_component_id
 , t.company_component_id
 , t.name
 , t.valid_cpy_compnt_type_cs_name
 FROM RCTE_COMP_COMP AS cte
 JOIN dbo.cs_company_component AS t 
   ON t.parent_company_component_id = cte.company_component_id
) 
SELECT * 
FROM RCTE_COMP_COMP cte
ORDER BY cte.base_id, cte.lvl