hrishi hrishi - 25 days ago 6
MySQL Question

Join two tables on different columns

I have two tables like below in mysql

city

id from to

1 101 102


travel

id name

101 ABC

102 XYZ


I am trying to join table such that I will get ABC as source and XYZ as destination. I tried multiple combinations but not getting expected result

Answer

Join the travel table twice with different alias names

select c.id, 
       t1.name as city_from, 
       t2.name as city_to
from city c
join travel t1 on t1.id = c.`from`
join travel t2 on t2.id = c.`to`