I have a table passenger_count
| pas1_id | pas2_id | count |
|---|---|---|
| 1 | 6 | 2 |
| 14 | 37 | 4 |
that connects by pas_id with another table passenger:
| id | name |
|---|---|
| 1 | Bruce k |
| 2 | George L |
| 3 | Li Wo |
| 4 | Don Wa |
How to replace pas1_id, pas2_id with actual names from passenger table?
This self join query does not work:
select p.name, p2.name, count from passenger p on p.id = pas1_id -- and p.id = pas2_id inner join passenger p2 on p2.id = pas2_id where p.name < p2.name
Advertisement
Answer
Just join passenger to passenger_count twice.
SELECT p1.name,
p2.name,
pc.count
FROM passenger_count pc
INNER JOIN passenger p1
ON p1.pas_id = pc.pas1_id
INNER JOIN passenger p2
ON p2.pas_id = pc.pas2_id;