0

I have a table which stores 2 calendars events that each row is a meeting with event_id (int), calendar_id (int - 1/2), start (datetime) and end (datetime).

Desired output: New table with event_id from calendar_id(1), start and end that has no event overlapping with events from calendar_id(2).

For example, for the following input: table example

eventid | calendaid | start        | end
    101 |         1 | 1/1/10 20:00 | 1/1/10 22:00 
    102 |         2 | 1/1/10 20:00 | 1/1/10 21:00 
    103 |         1 | 1/1/10 22:00 | 1/2/10 02:00 
    104 |         2 | 1/1/10 22:00 | 1/2/10 02:00 
    105 |         1 | 1/2/10 01:00 | 1/2/10 05:00
    106 |         2 | 1/2/10 01:00 | 1/2/10 02:00 
    107 |         1 | 1/2/10 05:00 | 1/2/10 06:00 
    108 |         2 | 1/2/10 05:00 | 1/2/10 08:00 
    109 |         1 | 1/2/10 06:00 | 1/2/10 08:00
    110 |         2 | 1/2/10 03:00 | 1/2/10 04:00

Output should be the following:

eventid | start        | end 
    101 | 1/1/10 21:00 | 1/1/10 22:00
    105 | 1/2/10 02:00 | 1/2/10 03:00 
    105 | 1/2/10 04:00 | 1/2/10 05:00

Is there any way to perform such a query in MySQL?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
ravid007
  • 1
  • 1

1 Answers1

0

I think you can do this with a correlated subquery:

select c.*
from calendars c
where c.calendarid = 1 and
      not exists (select 1
                  from calendars c2
                  where c2.calendarid = 2 and
                        c2.start < c.end and
                        c2.end > c.start
                 );

The overlap logic assumes that events that overlap only on the start/end datetimes are not overlapping.

There is no particularly efficient way to implement this logic. An index on (calendarid, start, end) might help.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786