How do I go about type iterations in a SQL Query? I have a Dependency table that has a ParentID, ParentEntityType, ChildID, and ChildEntityType along with additional fields. The EntityTypes refer to a variety of object types (Nodes, Groups, Transactions, etc.) which are all stored on their respective tables with the ID being the key. These tables contains some additional fields that I need to grab (e.g. the Name of the node). 

For example: a Dependency record, with omitted fields, looks like


My question is how to smartly go about going through all the types. What I came up with was a left join for each type filtered with a WHERE and coalescing the resulting mass but that seems awful and would need to be duplicated for the child. I feel there has to be a better way but I'm a bit stumped.

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
    LEFT JOIN (SELECT  c.ContainerID
        ,c.Status
        ,c.Name
        FROM dbo.containers as c) as con on d.ParentNetObjectID = con.ContainerID AND d.ParentEntityType = 'Orion.Groups'
   LEFT JOIN (SELECT n.NodeID... --A left Join for every flavor that looks the same as above
WHERE 1=1 --Not important for this
ORDER BY ParentName
I would just join the tables directly
Coalesce is fine here I think  

SELECT d.[Name] as DepName
      ,[ParentEntityType]
      ,[ParentNetObjectID]
      ,[ChildEntityType]
      ,[ChildNetObjectID]
      ,coalesce(con.Name, nod.Caption, trans.name, app.name) as [ParentName]
      ,coalesce(con.status, nod.status, trans.laststatus, app.Availability) as [ParentStatus]
FROM dbo.Dependencies as d
LEFT JOIN dbo.containers as con 
  on d.ParentNetObjectID = con.ContainerID 
 AND d.ParentEntityType = 'Orion.Groups'
LEFT JOIN  ...  
ORDER BY ParentName 


You could put the 3 tables in a view or cte  

select "orion" as type, 
        c.ContainerID
       ,c.Status
       ,c.Name
 FROM dbo.containers as c 
union all 
...