CODEWITHSUNDEEP
mysqlsql
lightalex
lightalex

Reputation: 879

Limit JOIN to one result

I'm trying to write a SQL query that make me crazy...

I have a table establishment with columns id, latitude, longitude. Another table event with columns id, establishment_id, date.

I would like to get a single event for each establishment, and more precisely the closest one in time. For now I have :

SELECT et.latitude, et.longitude, ev.*
FROM establishment et
    JOIN event ev ON et.id = ev.establishment_id
WHERE ev.date_event >= CURDATE()
ORDER BY ev.date_event ASC

But this is giving me the full list of events ordered perfectly. I need to get only one event for each establishment_id thought...

Do you have any idea if it is possible?

Upvotes: 1

Views: 66

Answers (2)

xenodevil
xenodevil

Reputation: 604

Assuming MAX(ev.date_event) gives you the the event closest in time, this should be able to do the job.

BTW, you didn't mention that you need events from current date only, if this isn't a requirement then WHERE clause should be removed.

(not executed so look out for syntax)

SELECT et.latitude, et.longitude, ev.establishment_id, MAX(ev.date_event)
FROM establishment et
    JOIN event ev ON et.id = ev.establishment_id
WHERE ev.date_event >= CURDATE()
GROUP BY et.latitude, et.longitude, ev.establishment_id

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53880

Here's how you do it with a JOIN (can also be done with a subquery, however, generally this method outperforms the subquery method):

SELECT et.latitude, et.longitude, ev.*
FROM establishment et
  JOIN event ev1
  ON ev1.establishment_id = et.id
  AND ev1.date_event >= CURDATE()
  LEFT JOIN event ev2
  ON ev2.establishment_id = et.id
  AND ev2.date_event >= CURDATE()
  AND ev2.date_event < ev1.date_event
WHERE ev2.establishment_id IS NULL

It first does the proper join for events >= current date, then does another left join to see if there is another event with the same establishment_id that's before the first, and filters it out if there is.

Upvotes: 4

Related Questions