CODEWITHSUNDEEP
mysql
ctown4life
ctown4life

Reputation: 965

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

Upvotes: 1

Views: 64

Answers (2)

ctown4life
ctown4life

Reputation: 965

A friend helped me find this solution:

SELECT e.* 
FROM Events e 
LEFT JOIN (
    SELECT start_date,
    hostid,
    count(*) cnt,
    AVG(Cast(recurring as decimal)) recur
    FROM Events
    GROUP BY start_date, hostid
) r
ON e.start_date = r.start_date 
AND e.hostid = r.hostid
WHERE (e.recurring = 0 and r.recur <> 1)
OR r.recur = 1 OR r.cnt = 1
ORDER BY e.id

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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 :-)

Upvotes: 1

Related Questions