CODEWITHSUNDEEP
mysqlsql
ERJAN
ERJAN

Reputation: 24500

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

Upvotes: 0

Views: 2227

Answers (2)

Alessio Cantarella
Alessio Cantarella

Reputation: 5201

SELECT p1.name,
  p2.name,
  pc.count
FROM passenger_count AS pc
JOIN passenger AS p1 ON pc.pas1_id = p1.id
JOIN passenger AS p2 ON pc.pas2_id = p2.id

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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;

Upvotes: 2

Related Questions