'SQL Join to return data from The Same Column in the same table to two diffrent rows in result (Star Wars Example)

Newbie question about joining tables. I want to retrieve a name from a column TWICE in a SQL statement, and I'm running in circles.

I Have two Tables - "Company" & "People"

Table -"People"

ID Name Phone
1 Luke 555-1212
2 Leia 555-1234
3 Han 999-8888
4 Anikin 888-9876
5 Obi-wan 555-1212
6 R2-D2 #% - **!?

Table - "Company"

ID CompanyName PrimaryContact AltContact
1 Speeders R Us 5 1
2 Droid Repair World 6 4
3 Luke's Second Hand Store 1 4
4 Cloak World 4 5
5 Ye Old Blaster Shoppe 3 2

If I want to get a result that gives BOTH the Contact Names for a Company, How would I do it?

I can get the PrimaryContact to JOIN Properly using something like...

SELECT C.*, P.Name as 'Primary' 
FROM `Company` C 
Join People P on  
C.PrimaryContact = P.ID  
WHERE C.ID =3

which successfully returns

ID CompanyName PrimaryContact AltContact Primary
3 Luke's Second Hand Store 1 4 Luke

But for the life of me, I can't figure out how to modify this SQL to also return "Anikin" as the Alternate Contact. Is this an example of where a UNION statement would help?

sqljoinunion


Solution 1:[1]

You can join to the same table multiple times, just give a new alias every time.

Join People P on  C.PrimaryContact = P.ID 

Join People P1 on  C.AltContact = P1.ID 

 Join People altcontact on  C.AltContact = altcontact.ID 

Join People P256 on  C.yetanotheralternateContact = P256.ID

Solution 2:[2]

You need to to join for ecervy contact another Persons table

SELECT C.ID,C.CompanyName, P.Name as 'Primary' , P.Phone As 'primary_Phone', P2.Name on 'Alternative', P2.Phone as 'Alternatibe_Phone
FROM `Company` C 
Join People P on  
C.PrimaryContact = P.ID  
Join People P2 on  
C.AltContact = P2.ID  
WHERE C.ID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Twelfth
Solution 2 nbk

Related Questions