CODEWITHSUNDEEP
mysqldatabaserdbms
Jagadesh jakes
Jagadesh jakes

Reputation: 85

mysql query for multiple foreign keys

mysql tables are as follows


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


    +------------+----------------+----------------+
    | booking_id | boarding_point | dropping_point |
    +------------+----------------+----------------+
    |          1 |     coimbatore |          erode |
    |          2 |        chennai |     coimbatore |
    +------------+----------------+----------------+

can anyone please suggest me the query to display like above.

Upvotes: 0

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521299

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;

enter image description here

Demo

Upvotes: 2

Related Questions