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;