Three way join in sql

Question

I have three tables titled "Guest", "Guest_Address" and "Acutal_Address". Guest_Address is a linking table between guest and acutal_address. This is what I have so far.

  SELECT GUEST_ADDRESS.ADDRESS_CODE,(GUEST_FNAME+' '+GUEST_LNAME) AS GUEST_NAMES
  FROM GUEST JOIN GUEST_ADDRESS 
  ON GUEST.ADDRESS_NUM = GUEST_ADDRESS.ADDRESS_NUM;

This only joins the Guest and Guest_address table, but I need to join Guest and Acutal_Address. Here is the ERD.

Answer 1

What you want to do is do an additional join to the actual_address table like so :

  SELECT GUEST_ADDRESS.ADDRESS_CODE,(GUEST_FNAME+' '+GUEST_LNAME) AS GUEST_NAMES
  FROM GUEST 
  JOIN GUEST_ADDRESS ON GUEST.ADDRESS_NUM = GUEST_ADDRESS.ADDRESS_NUM
  JOIN ACTUAL_ADDRESS ON GUEST_ADDRESS.ADDRESS_CODE = ACTUAL_ADDRESS.ADDRESS_CODE

Mind if I ask why you have that linking table? Can guests have multiple addresses? If that is the case I would change the join from GUEST_ADDRESS to GUEST based on GUEST_ID, rather than ADDRESS_NUM as using ADDRESS_NUM makes the relationship between the two tables a one-to-one relationship rather than one-to-many

Also worth noting that the query above will not return a record for the guest if there is no address in the ACTUAL_ADDRESS table or link in the GUEST_ADDRESS table due to the JOIN. If you want it to return guest details regardless of address you can simply change the JOIN to LEFT JOIN

Answer 2

There is a many-to-many relationship between Guest and ActualAddress via the auxilary table GuestAddress. So to join then all together into one set do:

select * 
from Guest 
left join GuestAddress on GuestAddress.Guest_ID = Guest.Guest_ID 
left join ActualAddress on ActualAddress.AddressCode = GuestAddress.Address_Code

Then use the where clause to filter the set down to a smaller one (sub-set), if need be