How to query the 3 tables below to detect resource scheduling conflicts? Conflicts occur when two
events with overlapping date/time ranges require common
CREATE TABLE `events` ( id int(11) AUTO_INCREMENT, `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), ); CREATE TABLE event_participations ( event int(11), # refers to events.id participant int(11), # refers to participants.id PRIMARY KEY (event,participant) );
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 involve mutually exclusive sets of
participants. How to query for event-pairs that require the same participants at the same time?