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
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.
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