Neil Neil - 2 months ago 5
MySQL Question

MYSQL - Join with multiple dates - only get first from join

so i have 3 tables in MySql

events

| ID | event_name |
-------------------
| 1 | test |
| 2 | test2 |
| 3 | test3 |


sp_events

| ID | event_ID | show_in_grid |
--------------------------------
| 5 | 1 | 1 |
| 6 | 2 | 1 |


sp_event_dates

| ID | sp_event_ID | start_date |
------------------------------------------
| 1 | 5 | 2016-10-31 14:00:00 |
| 2 | 5 | 2016-11-01 14:00:00 |
| 3 | 5 | 2016-11-02 14:00:00 |
| 4 | 6 | 2016-12-01 14:00:00 |
| 5 | 6 | 2016-12-02 14:00:00 |


so Im trying to join the sp_event_dates table but i only want the first result and dont want a duplicate for every result.

the SQL Ive tried is

SELECT events.*, sp_event_dates.start_date FROM events
JOIN sp_events
ON sp_events.event_ID=events.ID
JOIN sp_event_dates ON sp_event_dates.sp_event_ID =
(SELECT dd.ID FROM sp_event_dates dd WHERE sp_events.ID =
dd.sp_event_ID ORDER BY dd.start_date ASC LIMIT 1)
WHERE sp_events.show_in_grid=1;


This doesn't work as intended. I would expect the results to be as below:

| ID | event_name | start_date |
-----------------------------------------
| 1 | test | 2016-10-31 14:00:00 |
| 2 | test2 | 2016-12-01 14:00:00 |


I do eventually plan to add a where clause on the start_date but just trying to get this to work first. Can anyone see what I'm doing wrong?

My query returns no results currently

jva jva
Answer

Just use a GROUP BY + MIN:

SELECT events.id
  ,events.event_name
  ,min(sp_event_dates.start_date) As start_date
FROM events 
JOIN sp_events 
ON sp_events.event_ID=events.ID 
JOIN sp_event_dates ON sp_event_dates.sp_event_ID = sp_events.ID
WHERE sp_events.show_in_grid=1
GROUP BY events.id
    ,events.event_name
Comments