SQL Server: querying hierarchical and referenced data

Question

I'm working on an asset database that has a hierarchy. Also, there is a "ReferenceAsset" table, that effectively points back to an asset. The Reference Asset basically functions as an override, but it is selected as if it were a unique, new asset. One of the overrides that gets set, is the parent_id.

Columns that are relevant to selecting the heirarchy:
Asset: id (primary), parent_id
Asset Reference: id (primary), asset_id (foreignkey->Asset), parent_id (always an Asset)
---EDITED 5/27----

Sample Relevent Table Data (after joins):

   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

the id < 0 (like the last row) signify assets that are referenced. Referenced assets have a few columns that are overidden (in this case, only parent_id is important).

The expectation is that if I select all assets from april, I should do a secondary select to get the entire tree branches of the matching query:

so initially the query match would result in:

    4       4       suit_banker         3          april      texture

Then after the CTE, we get the complete hierarchy and our result should be this (so far this is working)

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

and you see, the parent of id:-5 is there, but what is missing, that is needed, is the referenced asset, and the parent of the referenced asset:

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

Currently my solution works for this, but it is limited to only a single depth of references (and I feel the implementation is quite ugly).

---Edited---- Here is my primary Selection Function. This should better demonstrate where the real complication lies: the 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 temp table (#temp) and finds all the elements of the hierarchy. The strategy I employed was this:

1. Select the entire system heirarchy into a temp table (#treeIDs) represented by a comma separated list of each entire tree branch
2. Get entire heirarchy of assets matching query (from #temp)
3. Get all reference assets pointed to by Assets from heirarchy
4. Parse the heirarchy of all reference assets

This works for now because reference assets are always the last item on a branch, but if they weren't, i think i would be in trouble. I feel like i need some better form of recursion.

Here is my current code, which is working, but i am not proud of it, and I know it is not robust (because it only works if the references are at the bottom):

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 Reference Assets in the branches (Reference assets have negative id values, hence the 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 the branches of anything found 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've tried to just show the relevant code. I am super grateful to anyone who can help me find a better 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's place in the hierarchy from this query only
--not in the overall reality of the row's 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