1

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 Record

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.