Mysql join two tables with different row on left side and same row on right side

Question

I have two table like this:

table1_ride
--------
id                 ride id
from_which_city    city id
to_city            city id

table2_city
--------
id                 city id
name               city name

What I want is when I submit query SELECT * FROM ride I want to show me ride_id, from_which_city, to_city like this:

1 Manchester Liverpool 

instead of

1 8 3 Where 8 = ManchesterID and 3 = LiverpoolID

I tried left join

SELECT * FROM ride LEFT JOIN city ON ride.from_which_city = city.id

and it's working for from_which_city. How to do this to work for both - from_which_city and to_city.

I didnt find case where left join is like: t1.b = t2.a AND t1.c = t2.a.

Thanks in advance!

Answer 1

Try this:

SELECT r.id, c1.name, c2.name
FROM table1_ride r
JOIN table2_city c1 on r.from_which_city=c1.id
JOIN table2_city c2 on r.from_which_city=c2.id

Answer 2

Use table aliases:

SELECT ride.id, fromcity.name, tocity.name
FROM ride
LEFT OUTER JOIN city fromcity ON ride.from_which_city = fromcity.id
LEFT OUTER JOIN city tocity ON ride.to_city = tocity.id

Answer 3

Join on the table2_city table twice and use an alias:

SELECT table1_ride.id, fc.name as from_city_name, tc.name as to_city_name
FROM table1_ride
INNER JOIN table2_city AS fc ON
    table1_ride.from_which_city=fc.id
INNER JOIN table2_city AS tc ON
    table1_ride.to_which_city=tc.id

(replace inner with left outer if necessary...).

Answer 4

SELECT c.id, m1.name, m2.name FROM mytabl1 as c, mytabl2 as m1, mytabl2 as m2
WHERE
c.cfrom = m1.city AND c.cto = m2.city
ORDER BY c.id

If I use above code below is what I get and this is what you were expected.

id   name        name
1    City 1      City 2
2    City 3      City 4
3    City 1      City 3
4    City 2      City 4