MySQL连接两个表，左侧不同的行，右侧相同的行

问题描述

我有两个这样的表:

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

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

我想要的是提交查询SELECT * FROM ride时要向我展示的ride_id, from_which_city, to_city like此内容:

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 

代替

1 8 3 Where 8 = ManchesterID and 3 = LiverpoolID

我尝试了左加入

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

，它适用于from_which_city.如何同时对from_which_city和to_city起作用.

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

我没有发现左联接类似的情况:t1.b = t2.a AND t1.c = t2.a.

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

提前谢谢！

推荐答案

加入table2_city表两次并使用别名:

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...).

这篇关于MySQL连接两个表，左侧不同的行，右侧相同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！