Skip to content
Advertisement

how to replace ids with names in sql from another table?

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement