Returning all components multiple levels under a parent component

I have a hierarchical database out of which I would like to return all components in an asset with a specific common parent component.

Components in table astComponents are linked to their parent components with a ParentComponentId referring to the Id of other components. This, however, only shows the parent one level up.

Additionally, all components are linked to an asset by an AssetId key, linking to the Id key in table astAssets.

I am able to make a simple query (below) to retrieve all components from a specific asset. I can’t figure out how to return all components from a specific asset sharing the same parent component. This parent component may (relative to a child component) 1 to multiple levels up.

Select C.Id, C.Code, ParentC.Id, ParentC.Code, A.Code
From astComponents C

Join astAssets A ON A.Id = C.AssetId
Join astComponents ParentC ON ParentC.Id = C.ParentComponentId

Where A.Code = '2018100000'

The above query returns components and their parents one level up. Were I to specify the parent code, it would only return the child components one level below this specified component, instead of all levels down.

Answer

Okay, without any data to work with there’s a lot of guesswork going on here. I created some data:

DECLARE @astComponents TABLE (Id INT, Code VARCHAR(50), ParentComponentId INT, AssetId INT);
DECLARE @astAssets TABLE (Id INT, Code VARCHAR(50));
INSERT INTO @astAssets SELECT 1, '2018100000';
INSERT INTO @astAssets SELECT 2, '2018100001';
INSERT INTO @astComponents SELECT 1, 'Power Supply', NULL, 1;
INSERT INTO @astComponents SELECT 2, 'Fan', 1, NULL;
INSERT INTO @astComponents SELECT 3, 'Screw', 2, NULL;
INSERT INTO @astComponents SELECT 4, 'Heat Tape', 1, NULL;
INSERT INTO @astComponents SELECT 5, 'Magnet', NULL, 2;
INSERT INTO @astComponents SELECT 6, 'Iron', 5, NULL;

What you can do with this is to use recursion to map out all the components that share a common parent, and record which asset they belong to, e.g.:

--Make a recursive list of components sharing a common parent
WITH cte AS (
    SELECT
        Id,
        ParentComponentId,
        AssetId
    FROM
        @astComponents
    WHERE
        ParentComponentId IS NULL
    UNION ALL
    SELECT
        c.Id,
        c.ParentComponentId,
        cte.AssetId
    FROM
        @astComponents c
        INNER JOIN cte ON cte.Id = c.ParentComponentId)
SELECT * FROM cte;

I bet your tables don’t work like this, so you would need to tweak this to suit. In my case I get the following results:

Id  ParentComponentId   AssetId
1       NULL        1
5       NULL        2
6       5           2
2       1           1
4       1           1
3       2           1

So I can see that components #1 and #5 have no parents, so these are “top level” components, and I can also see which components link to which assets.

Now it’s simply a case of replacing the SELECT * FROM cte part of my query with something like this:

SELECT 
    cte.Id,
    c.Code,
    c.ParentComponentId
FROM 
    cte
    INNER JOIN @astComponents c ON c.Id = cte.Id
    INNER JOIN @astAssets a ON a.Id = cte.AssetId
WHERE
    a.Code = '2018100000';

Gives me all the components that share a parent where the parent has an asset code of 201810000:

Id  Code    ParentComponentId
1   Power Supply    NULL
2   Fan 1
4   Heat Tape   1
3   Screw   2