MYSQL LEFT JOIN 3 tables include NULL sets
Tried to 'reverse engineer' other SO users questions/answers to my question but couldn't. JOINS still elude me.
I have three tables: race (a list of all races), result (results for each race), driver (driver information). Not all drivers have raced in all races at a specific track.
race table
| race_id | track_id | year | complete |
+-----------+------------+--------+----------+
| 1 | 1 | 2006 | 1 |
| ... | ... | ... | ... |
| 21 | 1 | 2007 | 1 |
| ... | ... | ... | ... |
| 135 | 1 | 2008 | 1 |
| ... | ... | ... | ... |
| 215 | 1 | 2009 | 1 |
| ... | ... | ... | ... |
| 292 | 1 | 2010 | 1 |
| ... | ... | ... |... |
| 351 | 1 | 2011 | 0 |
+-----------+------------+--------+----------+
result table
| race_id | driver_id | finish |
+---------+-----------+--------+
| 135 | 1 | 15 |
| ... | ... | ... |
| 292 | 1 | 6 |
+---------+-----------+--------+
driver table
| driver_id | name |
+-----------+------------+
| 1 | Joe Driver |
+-----------+------------+
I want a result set that shows:
| name | race_id | year | finish |
+------------+---------+--------+--------+
| Joe Driver | 21 | 2007 | NULL |
| Joe Driver | 135 | 2008 | 15 |
| Joe Driver | 215 | 2009 | NULL |
| Joe Driver | 292 | 2010 | 6 |
+------------+---------+--------+--------+
Joe Driver only raced in 2 of the 4 races that were at track_id = 1, after 2006 and only includes races that are complete (1)
This is my current query:
SELECT driver.driver, race.id, race.year, result.finish
FROM race
LEFT JOIN result ON race.id = result.race_id
LEFT JOIN driver ON result.driver_id = driver.driver_id
WHERE race.track_id = 1
AND ra开发者_如何学Pythonce.year > 2006
AND race.complete = 1
AND driver.driver_id = 1
ORDER BY race.id ASC
The result I get only shows the races the driver was in:
| name | race_id | year | finish |
+------------+---------+--------+--------+
| Joe Driver | 135 | 2008 | 15 |
| Joe Driver | 292 | 2010 | 6 |
+------------+---------+--------+--------+
I'm sure I'm overlooking a simple rule to JOINS?
Thanks!
SELECT driver.driver, race.id, race.year, result.finish
FROM race
CROSS JOIN
driver
LEFT JOIN
result
ON result.race_id = race.id
AND result.driver_id = driver.driver_id
WHERE race.track_id = 1
AND race.year > 2006
AND race.complete = 1
AND driver.driver_id = 1
ORDER BY
race.id ASC
精彩评论