Weird MySQL query that's wracking my brain

Question

Okay, I have two tables. One is events and the other is occurs. The event table is basic, the event, it's ID, its description, etc... The occurs table is linked to the events table using the event ID; its basically a table of times for the event. For instance, if an event occurs every Tuesday, then an event will have an occurrence every Tuesday night in the database.

By default, the a row in the events table does not have any timestamps in it, those are all in the occurs table. However, for some reason or another I have the need to get the results of the events table and also have a timestamp on it. So what I would like to do is use a query to the events table, linked to a single row in the occurs table. The part I am stuck on is which occurrence to get.

If the event has any occurrence after the current time, I would like to simply get the next upcoming occurrence. However, if it is a past event, with no upcoming occurrence, I would like to get the most recent occurrence (last).

Anyone got any tips or hints on how to get this done?

Would love it if you could go to http://sqlfiddle.com/ and build out a test db for us to use. — Fallenreaper, Mar 09 '16 at 19:52
Take a look at this [question](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1) about nested SQL queries — Alon Eitan, Mar 09 '16 at 19:56

SunKnight0 · Accepted Answer · 2016-03-09T21:47:23.177

1

One way to do it is JOIN the occurs table twice, one unconditional and get the max timestamp and one conditional on timestamp > current time and get the min timestamp. Then if the second one is not null use that otherwise use the first one.

This is NOT REAL CODE, just a quick pseuodcode example:

SELECT events.eventid,
  events.description,
  CASE WHEN MIN(ob.timestamp) IN NOT NULL THEN MIN(ob.timestamp) ELSE MAX(oa.timestamp) END AS timestamp
FROM events
  LEFT JOIN occurs oa ON events.id=oa.eventid
  LEFT JOIN occurs ob ON events.id=ob.eventid and ob.timestamp>Now()
GROUP BY events.eventid, events.description

edited Mar 09 '16 at 21:47

answered Mar 09 '16 at 20:00

SunKnight0

3,331
1
10
8

Hmm... could you add an example of this? – Jason Axelrod Mar 09 '16 at 20:05
see my answer below. You can test at sqlfiddle.com: http://sqlfiddle.com/#!9/06b2aa/1/0 – Walter_Ritzel Mar 09 '16 at 20:29
@Jason: I am more used to MS SQL Server than MySQL so I can't do it on the fly, sorry. If you still need it by Friday I will have time to do it then. – SunKnight0 Mar 09 '16 at 21:40

Walter_Ritzel · Answer 2 · 2016-03-09T20:30:10.433

Try this:

create table events (id integer, descr varchar(20));
create table occurs (event_id integer, occur_date timestamp);
insert into events values (1, 'EV1');
insert into events values (2, 'EV2');
insert into occurs values (1, str_to_date('2016-03-09 05:40:00 AM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 08:40:00 AM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 11:40:00 AM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 05:40:00 PM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 06:40:00 PM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 07:40:00 PM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 09:40:00 PM','%Y-%m-%d %r'));
insert into occurs values (1, str_to_date('2016-03-09 10:40:00 PM','%Y-%m-%d %r'));
insert into occurs values (2, str_to_date('2016-03-09 05:34:00 AM','%Y-%m-%d %r'));
insert into occurs values (2, str_to_date('2016-03-09 08:34:00 AM','%Y-%m-%d %r'));
insert into occurs values (2, str_to_date('2016-03-09 11:34:00 AM','%Y-%m-%d %r'));
insert into occurs values (2, str_to_date('2016-03-09 02:34:00 PM','%Y-%m-%d %r'));

#solution
select e.id, e.descr, coalesce(min(o2.occur_date),max(o.occur_date)) as time_stamp, now()
from events e left join occurs o on e.id = o.event_id
left join occurs o2 on e.id = o2.event_id and o2.occur_date > now()
group by e.id, e.descr

Weird MySQL query that's wracking my brain

2 Answers2