I would like to query the 4 tables defined below to detect resource scheduling conflicts. Conflicts occur when two
reservations) require common
participants also have overlapping date/time ranges.
CREATE TABLE `events` ( reservation int(11), # refers to reservations.id `date` date, # date on which the event occurs start_minute smallint(6), # minute of day on which event starts end_minute smallint(6), # minute of day on which event ends ); CREATE TABLE participants ( id int(11) AUTO_INCREMENT, `name` varchar(255), `count` smallint(6), ); CREATE TABLE reservations ( id int(11) AUTO_INCREMENT, `name` varchar(255), ); CREATE TABLE reservation_participations ( reservation int(11), # refers to reservations.id participants int(11), # refers to participants.id PRIMARY KEY (reservation,resource) );
I think the following query would detect overlapping events:
SELECT * FROM events e CROSS JOIN events e2 WHERE e.date=e2.date AND e.start_minute<e2.end_minute AND e2.start_minute<e.end_minute
However, overlaps do not imply conflicts as the two
events may require mutually exclusive sets of
participants. So how to query event-pairs that overlap in both time and participants? Is it possible to do this in a single, reasonably efficient query?
(Eventually, I would like to also list (for each conflicting event-pair) the participants under contention, perhaps using something like
GROUP_CONCAT, but that may be a different question.)