I tried to "reconstruct" other questions and answers of SO users to my question, but could not. The CONNECTIONS are still eluding me.
I have three tables: race (list of all races), result (results for each race), driver (driver information). Not all racers participated in all races on a particular track.
racing 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 need 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 participated in only two of the 4 races that were on track_id = 1 after 2006 and included only full races (1)
This is my current request:
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 race.year > 2006 AND race.complete = 1 AND driver.driver_id = 1 ORDER BY race.id ASC
The result that I get shows only the races in which the driver was:
| name | race_id | year | finish | +------------+---------+--------+--------+ | Joe Driver | 135 | 2008 | 15 | | Joe Driver | 292 | 2010 | 6 | +------------+---------+--------+--------+
I'm sure I missed a simple JOINS rule?
Thanks!