title: SQL Server: querying hierarchical and referenced data
body: <p>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.</p>
  
  <p>Columns that are relevant to selecting the heirarchy:<br>
  Asset: id (primary), parent_id<br>
  Asset Reference: id (primary), asset_id (foreignkey->Asset), parent_id (always an Asset)<br>
  ---EDITED 5/27----     </p>
  
  <p>Sample Relevent Table Data (after joins):</p>
  
  <blockquote>
  <pre><code>   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
  </code></pre>
  </blockquote>
  
  <p>the id &lt; 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).</p>
  
  <p>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:</p>
  
  <p>so initially the query match would result in:</p>
  
  <blockquote>
  <pre><code>    4       4       suit_banker         3          april      texture
  </code></pre>
  </blockquote>
  
  <p>Then after the CTE, we get the complete hierarchy and our result should be this (so far this is working)</p>
  
  <blockquote>
  <pre><code>    3       3       suit               null        march      shape
      4       4       suit_banker         3          april      texture
     -5       6       tie_red             4          march      texture
  </code></pre>
  </blockquote>
  
  <p>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:</p>
  
  <blockquote>
  <pre><code>    5       5       tie                null        march      shape
      6       6       tie_red             5          march      texture
  </code></pre>
  </blockquote>
  
  <p>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).</p>
  
  <p>---Edited----
  Here is my primary Selection Function.  This should better demonstrate where the real complication lies: the AssetReference.  </p>
  
  <pre><code>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 &lt;= @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 &lt;= @showDeleted
  </code></pre>
  
  <p>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:</p>
  
  <ol>
  <li>Select the entire system heirarchy into a temp table (#treeIDs) represented by a comma separated list of each entire tree branch</li>
  <li>Get entire heirarchy of assets matching query (from #temp)</li>
  <li>Get all reference assets pointed to by Assets from heirarchy</li>
  <li>Parse the heirarchy of all reference assets</li>
  </ol>
  
  <p>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.  </p>
  
  <p>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):</p>
  
  <p>Step 1. build the entire hierarchy</p>
  
  <pre><code>;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
  </code></pre>
  
  <p>Step 2.  Select the branches of all assets that match the query</p>
  
  <pre><code>Select DISTINCT L.id into #RelativeIDs FROM #treeIDs
  CROSS APPLY dbo.SplitIDs(idList) as L
  WHERE #treeIDs.id in (Select id FROM #temp)
  </code></pre>
  
  <p>Step 3. Get all Reference Assets in the branches
  (Reference assets have negative id values, hence the id &lt; 0 part)</p>
  
  <pre><code>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 &lt; 0)
  </code></pre>
  
  <p>Step 4. Get the branches of anything found in step 3</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>I've tried to just show the relevant code.  I am super grateful to anyone who can help me find a better solution!</p>