CODEWITHSUNDEEP
mysqlsqljoinleft-join
Mihkel Mark
Mihkel Mark

Reputation: 115

How to display LEFT JOIN results with a condition coming from a third table?

I have the following tables in a database called racing:

RACE

MEETING

RESULT

And I've been given a task to do a query that gets all the race titles + off_times today AND a winning horse IF there is one.

I was given a hint that I have to use a LEFT JOIN to work on this, I managed to get the left join working, however I did not find a way to tie things to a certain date (meeting_date). So this gave me the winning horse of the results of all the races:

SELECT r.title, r.off_time, rlt.horse 
    FROM racing.race r
        LEFT JOIN racing.result rlt ON r.race_id=rlt.race_id;

How do I filter these to the results of the races of today, with a winning horse (position = 1) only if the race has happened, otherwise leave a NULL value?

Upvotes: 1

Views: 82

Answers (1)

Linger
Linger

Reputation: 15058

You will need to INNER JOIN to the meeting table:

SELECT r.title, r.off_time, rlt.horse 
FROM racing.race r
  INNER JOIN racing.meeting m ON r.meeting_id = m.meeting_id
  LEFT JOIN racing.result rlt ON r.race_id = rlt.race_id AND rlt.position = 1
WHERE m.meeting_date = CURDATE()

Upvotes: 1

Related Questions