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

而不是

1 8 3 Where 8 = ManchesterID and 3 = LiverpoolID

我试图与我团聚。

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

并且从_city <>/code>开始使用。如何为两者工作——<代码>,从_city 和to_city。

我确实发现,左派有类似之处:t1.b = t2.a 和 t1.c = t2.a。

提前感谢!

Answer 1

加入表2

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

(如果有必要,可替换内部左边。)

Answer 2

为此:

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 3

使用表格:

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

如果我使用以下代码,这就是我得到的东西,这就是大家的期望。

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

友情链接