0

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
Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34
ERJAN
  • 23,696
  • 23
  • 72
  • 146
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Feb 24 '21 at 21:57
  • 1
    Does this answer your question? [MySQL JOIN to replace IDs with value from another table](https://stackoverflow.com/questions/12431636/mysql-join-to-replace-ids-with-value-from-another-table) – mickmackusa Oct 04 '21 at 21:59
  • @Strawberry please support my dupe closure – mickmackusa Oct 04 '21 at 22:00

2 Answers2

2

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;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • thx, initially that's what i did but it did not work, the full problem is here https://sql-academy.org/en/trainer/tasks/64, my logic is this: self join pass_in_trip by trip field , then find those ids that are not the same, but still.. no luck – ERJAN Feb 25 '21 at 16:54
1
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
Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34