CODEWITHSUNDEEP
mysql
RichieW13
RichieW13

Reputation: 83

Compare most recent date in 2 MySQL tables

I have 2 tables:

events:

event_id | event_venue 
20160507 | 43 
20160501 | 47
20160424 | 22

results:

result_event | name | score
20160424 | John | 112 
20160424 | Billy | 113 
20160417 | Steve | 50

The event_id is just the date code.

The events table lists every scheduled event for the year. The results table only lists results that have happened so far.

I want to query the results table to see what the last result_date entered was, to see if it was from the final event of the year.

This isn't working...

SELECT MAX(event_date) as finale, MAX(results.result_date) as lastevent, event_venue
FROM events
JOIN results on results.event_id=events.id

With the goal being, if 'lastevent'='finale' then "FINAL" else event_venue.

Upvotes: 0

Views: 41

Answers (1)

Alfredo
Alfredo

Reputation: 171

You can use a simple subquery like this:

SELECT result_event 
FROM results 
WHERE result_event IN (
    SELECT MAX(event_id) FROM events
);

Then if the latest record in results table is from the latest event in events table you will get the event_id, if not you will get empty result.

Upvotes: 1

Related Questions