SQL Server: querying hierarchical and reference data

Question

I am working on an asset database that has a hierarchy. In addition, there is a "ReferenceAsset" table that actually points to an asset. The underlying asset basically functions as an override, but it is selected as if it were a unique new asset. One of the overrides that is set is parent_id.

Columns related to hierarchy selection:
Asset: id (primary), parent_id
Resource reference: id (primary), asset_id (foreignkey-> Asset), parent_id (always a Asset)
--- EDITED 5/27 ----

Examples of relevant table data (after merging):

id | asset_id | name | parent_id | milestone | type 3 3 suit null march shape 4 4 suit_banker 3 april texture 5 5 tie null march shape 6 6 tie_red 5 march texture 7 7 tie_diamond 5 june texture -5 6 tie_red 4 march texture 

identifier <0 (for example, the last line) means the assets referred to. Reference assets have several columns that are overridden (in this case only parent_id is important).

It is expected that if I select all assets from April, I must make a secondary choice to get all branches of the corresponding request tree:

therefore, initially matching requests will result in:

  4 4 suit_banker 3 april texture 

Then after CTE we get a complete hierarchy, and our result should be like this (while this works)

  3 3 suit null march shape 4 4 suit_banker 3 april texture -5 6 tie_red 4 march texture 

and you see that the parent id: -5 has, but the missing, which is necessary, is the reference asset and the parent of the reference asset:

  5 5 tie null march shape 6 6 tie_red 5 march texture 

My solution currently works for this, but it is limited to only one link depth (and I feel the implementation is pretty ugly).

--- Edited ---- Here is my main selection function. This should better demonstrate where the real complication is: AssetReference.

 Select A.id as id, A.id as asset_id, A.name,A.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName, L.name as locationName,AO.owner_name as ownerName, T.id as typeID, M.name as milestoneName, A.deleted as bDeleted, 0 as reference, W.phase_name, W.status_name FROM Asset as A Inner Join Type as T on A.type_id = T.id Inner Join Batch as B on A.batch_id = B.id Left Join Location L on A.location_id = L.id Left Join Asset A2 on A.parent_id = A2.id Left Join AssetOwner AO on A.owner_id = AO.owner_id Left Join Milestone M on A.milestone_id = M.milestone_id Left Join Workflow as W on W.asset_id = A.id where A.deleted <= @showDeleted UNION Select -1*AR.id as id, AR.asset_id as asset_id, A.name, AR.parent_id as parent_id, A.subPath, T.name as typeName, A2.name as parent_name, B.name as batchName, L.name as locationName,AO.owner_name as ownerName, T.id as typeID, M.name as milestoneName, A.deleted as bDeleted, 1 as reference, NULL as phase_name, NULL as status_name FROM Asset as A Inner Join Type as T on A.type_id = T.id Inner Join Batch as B on A.batch_id = B.id Left Join Location L on A.location_id = L.id Left Join Asset A2 on AR.parent_id = A2.id Left Join AssetOwner AO on A.owner_id = AO.owner_id Left Join Milestone M on A.milestone_id = M.milestone_id Inner Join AssetReference AR on AR.asset_id = A.id where A.deleted <= @showDeleted 

I have a stored procedure that takes a temporary table (#temp) and finds all the elements of the hierarchy. The strategy I used was as follows:

• Select the entire system hierarchy in the temp table (#treeID), represented by a comma-separated list of each solid tree branch.
• Get the entire hierarchy of assets matching the query (from #temp)
• Get all referenced assets that are referenced by assets in the hierarchy
• Analyze the hierarchy of all referenced assets.

This works now because the referenced assets are always the last item on the branch, but if they weren’t, I think I will have problems. I feel like I need a better form of recursion.

Here is my current code that works, but I'm not proud of it, and I know that it is not reliable (because it only works if the links are below):

Step 1. Build the entire hierarchy

 ;WITH Recursive_CTE AS ( SELECT Cast(id as varchar(100)) as Hierarchy, parent_id, id FROM #assetIDs Where parent_id is Null UNION ALL SELECT CAST(parent.Hierarchy + ',' + CAST(t.id as varchar(100)) as varchar(100)) as Hierarchy, t.parent_id, t.id FROM Recursive_CTE parent INNER JOIN #assetIDs t ON t.parent_id = parent.id ) Select Distinct h.id, Hierarchy as idList into #treeIDs FROM ( Select Hierarchy, id FROM Recursive_CTE ) parent CROSS APPLY dbo.SplitIDs(Hierarchy) as h 

Step 2. Select the branches of all assets that match the query.

 Select DISTINCT L.id into #RelativeIDs FROM #treeIDs CROSS APPLY dbo.SplitIDs(idList) as L WHERE #treeIDs.id in (Select id FROM #temp) 

Step 3. Get all the referenced assets in the branches (Source assets have negative id values, therefore id <0 part)

 Select asset_id INTO #REFLinks FROM #AllAssets WHERE id in (Select #AllAssets.asset_id FROM #AllAssets Inner Join #RelativeIDs on #AllAssets.id = #RelativeIDs.id Where #RelativeIDs.id < 0) 

Step 4. Get branches of everything that is in step 3

 Select DISTINCT L.id into #extraRelativeIDs FROM #treeIDs CROSS APPLY dbo.SplitIDs(idList) as L WHERE exists (Select #REFLinks.asset_id FROM #REFLinks WHERE #REFLinks.asset_id = #treeIDs.id) and Not Exists (select id FROM #RelativeIDs Where id = #treeIDs.id) 

I tried just to show the corresponding code. I am very grateful to everyone who can help me find the best solution!

Answer 1

 --getting all of the children of a root node ( could be > 1 ) and it would require revising the query a bit DECLARE @AssetID int = (select AssetId from Asset where AssetID is null); --algorithm is relational recursion --gets the top level in hierarchy we want. The hierarchy column --will show the row place in the hierarchy from this query only --not in the overall reality of the row place in the table WITH Hierarchy(Asset_ID, AssetID, Levelcode, Asset_hierarchy) AS ( SELECT AssetID, Asset_ID, 1 as levelcode, CAST(Assetid as varchar(max)) as Asset_hierarchy FROM Asset WHERE AssetID=@AssetID UNION ALL --joins back to the CTE to recursively retrieve the rows --note that treelevel is incremented on each iteration SELECT A.Parent_ID, B.AssetID, Levelcode + 1 as LevelCode, A.assetID + '\' + cast(A.Asset_id as varchar(20)) as Asset_Hierarchy FROM Asset AS a INNER JOIN dbo.Batch AS Hierarchy --use to get children, since the parentId of the child will be set the value --of the current row on a.assetId= b.assetID --use to get parents, since the parent of the Asset_Hierarchy row will be the asset, --not the parent. on Asset.AssetId= Asset_Hierarchy.parentID SELECT a.Assetid,a.name, Asset_Hierarchy.LevelCode, Asset_Hierarchy.hierarchy FROM Asset AS a INNER JOIN Asset_Hierarchy ON A.AssetID= Asset_Hierarchy.AssetID ORDER BY Hierarchy ; --return results from the CTE, joining to the Asset data to get the asset name ---that is the structure you will want. I would need a little more clarification of your table structure 

Answer 2

This will help you learn the basic structure of the table. There are two approaches that should work depending on your environment: SQL understands XML so that you can have your SQL as an xml structure or just have one table with each row element that has a unique primary key identifier and parent element. id is fk for parentid. Data for node are standard columns. You can use cte or a function that includes a computed column to determine the degree of nesting for each node. The limit is that a node can have only one parent.