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