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