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

Question

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

@Harshil This is dummy data as i don't want to put my actual DB info on here. I could maybe set this up as a real DB to get the output — Neil, Sep 21 '16 at 08:58
@Used_By_Already I am looking for the earliest at the moment — Neil, Sep 21 '16 at 08:58

score 2 · Accepted Answer · edited Sep 21 '16 at 09:57

2

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

edited Sep 21 '16 at 09:57

Neil

766
1
8
23

answered Sep 21 '16 at 09:02

jva

2,797
1
26
41

Sorry I have updated my question to include start_date in the SELECT. Its important that it returns the earliest date only – Neil Sep 21 '16 at 09:15
Thanks, Your answer guided me in the right direction, I got it to work by using the above – Neil Sep 21 '16 at 09:32

Noman · Answer 2 · 2016-09-21T09:36:26.780

0

You need groupby event id

SQL:

SELECT 
  events.ID,
  events.event_name,
  sp_event_dates.start_date 
FROM
  `events` 
  INNER JOIN sp_events 
    ON sp_events.event_ID = events.ID 
  INNER 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

Output:

ID  event_name  start_date

 1  test        2016-10-31 14:00:00  
 2  test2       2016-12-01 14:00:00

edited Sep 21 '16 at 09:36

answered Sep 21 '16 at 09:24

Noman

4,088
1
21
36

This doesn't seem to work for me i get error `Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.spEvd.start_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Neil Sep 21 '16 at 09:32
what mysql version are you using ? it is a mysql version problem.. seems like your mysql version doesn't support alias with group by.. ==> regarding the error please check this link http://stackoverflow.com/questions/34115174/i-am-getting-an-error-in-mysql-related-to-only-full-group-by-when-executing-a-qu – Noman Sep 21 '16 at 09:33
innodb_version - 5.7.13 - I tired again but got same error :( – Neil Sep 21 '16 at 09:38

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

2 Answers2