CODEWITHSUNDEEP
mysqlinner-join
Ivan Nikolov
Ivan Nikolov

Reputation: 235

How to get only one result per inner joined table

I have the following database structure:

Place table Place tabe

Place_has_Event table

Place_has_Event table

Event table

enter image description here

How can I get the closest(soonest) event for each place. So I need first event for all places. I want to get this in a single query.

Upvotes: 0

Views: 41

Answers (2)

Troy Jennings
Troy Jennings

Reputation: 432

You want to select min(start) to get the lowest date (this assumes that events in the past are pruned, or else you can use a where clause to filter past events), and group by location

This will return the event with the lowest date for each location.

Some syntax may be off, as my experience is with sql-server rather than mysql.

Select Event.name, min(Event.start) 
From Event Inner Join Place_has_Event On event.ID = Place_has_Event.Event_ID
Where Event.start > getdate() 
Group By Place_has_Event.Place_ID

Add select entries as desired.

Upvotes: 1

Punitha Subramani
Punitha Subramani

Reputation: 1477

Can you try it?

SELECT t1.* from tbl_name AS t1 
where t1.startdate=(
  select max(t2.startdate) from tbl_name as t2
)

Upvotes: 0

Related Questions