mysql tables are as follows
mysql表如下
+------------+----------------+----------------+
| booking_id | boarding_point | dropping_point |
+------------+----------------+----------------+
| 1 | 2 | 4 |
| 2 | 1 | 2 |
+------------+----------------+----------------+
+-------------+---------------+
| location_id | location_name |
+-------------+---------------+
| 1 | chennai |
| 2 | coimbatore |
| 3 | tiruppur |
| 4 | erode |
| 5 | salem |
+-------------+---------------+
boarding_point and dropping_point are foreign keys for location_id. Now I want the select query to display like
boarding_point和dropping_point是location_id的外键。现在我希望select查询显示为
+------------+----------------+----------------+
| booking_id | boarding_point | dropping_point |
+------------+----------------+----------------+
| 1 | coimbatore | erode |
| 2 | chennai | coimbatore |
+------------+----------------+----------------+
can anyone please suggest me the query to display like above.
任何人都可以建议我查询显示如上。
1 个解决方案
#1
2
Join the booking table twice to the location table:
将预订表加入位置表两次:
SELECT
b.booking_id,
t1.location_name,
t2.location_name
FROM booking b
INNER JOIN location t1
ON b.boarding_point = t1.location_id
INNER JOIN location t2
ON b.dropping_point = t2.location_id;
Demo
#1
2
Join the booking table twice to the location table:
将预订表加入位置表两次:
SELECT
b.booking_id,
t1.location_name,
t2.location_name
FROM booking b
INNER JOIN location t1
ON b.boarding_point = t1.location_id
INNER JOIN location t2
ON b.dropping_point = t2.location_id;