SQL multi level joins

Question

I need to join at least 4 tables. Table A is an Association table that contains a guid for Table B and C, Parentguid (B), Childguid (C). Table D contains information just for table C.

I need the results to look like this.

B - C - D

Monitor - Computer Name - Active

So the main thing is to show all of B table, only C table that is connected to B, and only D table this is associated with C.

I suspect I will need sub joins ( ). I am still a novice, it makes sense in my head but I can't seem to make the code work. I have played with joins for the past 2 days.

FROM vHWDesktopMonitor mon             -- [Symantec_CMDB2].[dbo].[ResourceAssociation]
join ResourceAssociation RM on mon._ResourceGuid = RM.ParentResourceGuid 
full outer join vComputer comp on RM.ChildResourceGuid = comp.Guid 
full outer join vAsset on RM.ChildResourceGuid = vAsset._ResourceGuid

If you want all data from table A and only data from table b that's in table A, and only data in table c that's in table B and only data in data in table D that's in Table C, then you want to use left joins. I find http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ especially useful for new SQL users as SQL is set based logic, mostly... — xQbert, Oct 10 '14 at 17:40
For me you have to use 3 inner joins mon JOIN RM JOIN comp JOIN vAsset — Joe Taras, Oct 10 '14 at 17:43

xQbert · Answer 1 · 2014-10-10T18:14:28.477

FROM vHWDesktopMonitor A
FULL OUTER JOIN ResourceAssociation B
  on A._ResourceGuid = B.ParentResourceGuid 
LEFT JOIN vComputer C
  on B.ChildResourceGuid = C.Guid 
LEFT JOIN vAsset D
  on C.ChildResourceGuid = D._ResourceGuid

So the above will return

All FROM A and ALL records from B (Full Outer between A, B)
Only records from C that are in B (LEFT Between B and C)
Only records from D that are in C (LEFT Between C and D)

However, if you apply any where clause limits, it could reduce records which otherwise would be kept due to the left or outer joins...

For example if A._ResourceGuid ='7' exists in A but isn't in B; and you set where B._ResourceGuid ='7' then the A record would otherwise would be kept due to the full outer join would then be excluded (making the full outer join the same as an INNER JOIN)!

a Full outer would return data like this:

if you add a where clause where B=7 then you may be expecting to get because of the full outer since you said return all records from both... A B 7 7 2

But you would end up getting

A B
7 7

Because the where clause occurs AFTER the full outer and therefore reduces the A.2 record. To compensate for this you either have to put teh limits on teh join before the full outer executes or handle it in a where clause (but this method is VERY messy and prone to error and performance issues)

So when using outer joins, you MUST put the limiting criteria on the JOIN itself like below..

FROM vHWDesktopMonitor A
FULL OUTER JOIN ResourceAssociation B
  on A._ResourceGuid = B.ParentResourceGuid 
 and B._resourceGuid = '7'
LEFT JOIN vComputer C
  on B.ChildResourceGuid = C.Guid 
LEFT JOIN vAsset D
  on C.ChildResourceGuid = D._ResourceGuid

You could also put it in the where clause but you must remember to account for all the outer joins on the table and include null values for the other (this is just messy and slow)

FROM vHWDesktopMonitor A
FULL OUTER JOIN ResourceAssociation B
  on A._ResourceGuid = B.ParentResourceGuid 
LEFT JOIN vComputer C
  on B.ChildResourceGuid = C.Guid 
LEFT JOIN vAsset D
  on C.ChildResourceGuid = D._ResourceGuid 
WHERE (A._ResourceGuid is null OR B.ParentResourceGuid ='7')

score 0 · Answer 2 · answered Oct 10 '14 at 18:05

If I understand you correctly either of these should work"

FROM vHWDesktopMonitor mon             -- [Symantec_CMDB2].[dbo].[ResourceAssociation]
left join ResourceAssociation RM on mon._ResourceGuid = RM.ParentResourceGuid 
left join vComputer comp on RM.ChildResourceGuid = comp.Guid 
left join vAsset on comp.Guid  = vAsset._ResourceGuid

or

FROM vHWDesktopMonitor mon             -- [Symantec_CMDB2].[dbo].[ResourceAssociation]
left join ResourceAssociation RM on mon._ResourceGuid = RM.ParentResourceGuid 
left join (select [list fields here] from vComputer comp 
join vAsset on comp.Guid  = vAsset._ResourceGuid) comp2
on RM.ChildResourceGuid = comp2.Guid

this should get you all the records from vHWDesktopMonitor and teh asscoiated records from ResourceAssociation with nulls for any records in vHWDesktopMonitor but not in ResourceAssociation. Then you get al teh records in vComputer that are also in ResourceAssociation. Finally you get al teh records in vAsset that are in vComputer. as alawys when you are getting all the records in teh first table, tehere will be nulls inteh fileds from other tables if you do not have an associated record.

If this doesn't work, perhaps you need to show us some sample data and expected results.

This looks promising. I just tried the above with appropriate fields. Errored out, but I will tweak it and respond back with what worked. — CO-VROD, Oct 13 '14 at 19:13
It is not going so well. I will keep at it for a few days. The end results should be. Monitor SN / Computer Name / Computer Asset Status. But I am getting Monitor SN / NULL — CO-VROD, Oct 14 '14 at 15:18
That is becasue you have monitor records that have no corresponding record in one or more of the other tables. That is normal left join behavior. — HLGEM, Oct 21 '14 at 13:44

SQL multi level joins

2 Answers2