CODEWITHSUNDEEP
mysqlsql
letsjak
letsjak

Reputation: 359

Joining tables in MySQL with multiple foreign keys

I have 2 tables

Rides (from one point to another)

+------+-------+-------+-------+-------+
| id   | start | end   | pointA| pointB|
+------+-------+-------+-------+-------+
| 1    | xxx   |    xx |    1  |    2  |
| 2    | xxx   |    xx |    2  |    1  |
+------+-------+-------+-------+-------+

Points

+------+-------------+
| id   | desc        |
+------+-------------+
| 1    | "Chicago"   |    
| 2    | "NYC"       |    
+------+-------------+

So for example:

When a person rides from point A to point B it gets registerd in the rides table. pointA and pointB are both FK's in the rides table from the table points

How is it possible to get an SQL output in MySQL like this for a ride where id = 1 for example:

+------+-------+-------+-------+-------+
| id   | start | end   | pointA| pointB|
+------+-------+-------+-------+-------+
| 1    | xxx   |    xx |Chicago|  NYC  |

Upvotes: 0

Views: 38

Answers (1)

Galz
Galz

Reputation: 6832

You can join the same table twice, by giving each one a different alias:

SELECT r.id, r.start, r.end, pA.desc as pointA, pB.desc as pointB
FROM rides r
JOIN points pA ON pA.id = r.pointA
JOIN points pB ON pB.id = r.pointB
WHERE r.id = 1;

Upvotes: 1

Related Questions