mysql - 加入两个mysql查询

Question

我有 3 个表:

表1

RouteID             int
departure_stop      int 
destination_stop    int 
Price               decimal(6,2) 

表2

StopID          int
Stop            varchar(50)
Cities_CityID   int

表3

CityID      int
City        varchar(50)     

我想获得所有低于 50 的路线。我想这样得到出发城市和目的地城市

City    City
Paris   London
Madrid  Zagred

到目前为止，我已经完成了两个查询，一个是出发城市，另一个是目的地城市查询，但不知道如何将它们并排连接起来。这是我的查询:

SELECT
    Cities.City
FROM Routes, Stops, Cities
WHERE (Stops.StopID = Routes.departure_stop AND Stops.Cities_CityID = Cities.CityID AND Routes.price < '50') 

和

SELECT
    Cities.City
FROM Routes, Stops, Cities
WHERE (Stops.StopID = Routes.destination_stop AND Stops.Cities_CityID = Cities.CityID AND Routes.price < '50')

所以问题是如何将它们并排连接在一起，以便我可以看到出发地和目的地？

Answer 1

Select
    DepartureCity.City AS 'Departure City',
    DestinationCity.City AS 'Destination City'
FROM Routes
INNER JOIN Stops DepartureStop    ON DepartureStop.StopID   = Routes.departure_stop
INNER JOIN Stops DestinationStop  ON DestinationStop.StopID = Routes.destination_stop 
INNER JOIN Cities DepartureCity   ON DepartureCity.CityID   = DepartureStop.Cities_CityID 
INNER JOIN Cities DestinationCity ON DepartureCity.CityID   = DestinationStop.Cities_CityID 

WHERE Routes.price < 50