ctown4life ctown4life - 2 months ago 5
MySQL Question

MySQL Selecting rows while grouping by multiple columns with a condition

I have a table of events. A host can create multiple events on a single day. Some of the events are recurring and some are not. I have a complex (for me) SELECT statement that I think requires conditionals some how and perhaps a subquery and I don't really know how to approach it.

Select Priority:


  1. If a host has more then one event on the same date and
    one of those dates is not recurring (0) then only select the non-recurring event for that host on that date.

  2. If a host has more then one event on the same date and all of the events on that date are recurring then select all events for that host on that date.

  3. If a host only has one event on a given date select it regardless of whether it is recurring or not.



Here is a sample Events table:

+----+--------+---------+------------+-----------+
| id | hostid | title | start_date | recurring |
+----+--------+---------+------------+-----------+
| 1 | 1 | Event1A | 2016-10-01 | 1 |
| 2 | 1 | Event1B | 2016-10-01 | 0 |
| 3 | 1 | Event1C | 2016-10-02 | 0 |
| 4 | 1 | Event1D | 2016-10-02 | 1 |
| 5 | 1 | Event1E | 2016-10-02 | 1 |
| 6 | 1 | Event1F | 2016-10-03 | 1 |
| 7 | 1 | Event1G | 2016-10-03 | 1 |
| 8 | 1 | Event1H | 2016-10-04 | 1 |
| 9 | 1 | Event1I | 2016-10-05 | 0 |
| 10 | 2 | Event2A | 2016-10-01 | 1 |
| 11 | 2 | Event2B | 2016-10-01 | 0 |
| 12 | 2 | Event2C | 2016-10-02 | 1 |
| 13 | 2 | Event2D | 2016-10-03 | 0 |
+----+--------+---------+------------+-----------+


Here are my desired Results:

+----+--------+---------+------------+-----------+
| id | hostid | title | start_date | recurring |
+----+--------+---------+------------+-----------+
| 2 | 1 | Event1B | 2016-10-01 | 0 |
| 3 | 1 | Event1C | 2016-10-02 | 0 |
| 6 | 1 | Event1F | 2016-10-03 | 1 |
| 7 | 1 | Event1G | 2016-10-03 | 1 |
| 8 | 1 | Event1H | 2016-10-04 | 1 |
| 9 | 1 | Event1I | 2016-10-05 | 0 |
| 11 | 2 | Event2B | 2016-10-01 | 0 |
| 12 | 2 | Event2C | 2016-10-02 | 1 |
| 13 | 2 | Event2D | 2016-10-03 | 0 |
+----+--------+---------+------------+-----------+


Here is a SQL Fiddle link with the table:

http://sqlfiddle.com/#!9/6427e/6

Answer

The task is rather simple: You want the record(s) with the minimum recurring flag per host and date.

select events.*
from
(
  select hostid, start_date, min(recurring) as recurring
  from events
  group by hostid, start_date
) chosen
join events on events.hostid = chosen.hostid
            and events.start_date = chosen.start_date
            and events.recurring = chosen.recurring
order by events.id;

Here is your SQL fiddle back: http://sqlfiddle.com/#!9/6427e/59 :-)

Comments