All geek questions in one place

Three ways to connect in sql

I have three tables called "Guest", "Guest_Address" and "Acutal_Address". Guest_Address is a table of links between guest and acutal_address. This is what I still have.

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 is only a connection to the Guest and Guest_address table, but I need to join Guest and Acutal_Address. Here is the ERD. enter image description here

+4
source share
2 answers

What you want to do is make an additional join to the actual_address table as follows:

  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

Remember, if I ask, why do you have this link table? Can guests have multiple addresses? If so, I would change the connection from GUEST_ADDRESS to GUEST based on GUEST_ID , not ADDRESS_NUM , since using ADDRESS_NUM makes the relationship between the two tables one-to-one, and not one-to-many

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

+10
source

There is a many-to-many relationship between Guest and ActualAddress through the GuestAddress helper table. So, to combine everything 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 to a smaller one (subset), if necessary

+2
source

Source: https://habr.com/ru/post/1487790/

More articles:


All Articles