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?
CodePudding user response：
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
CodePudding user response：
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