Oracle range and subquery

Question

I have a table that that I am trying to query against itself and I'm not sure how to go about it.

Table name: schedule

• user_id
• startdate
• enddate
• sequencyID

The situation is I have a number of rows where the user_id = 0. This represents an open schedule that someone can claim. If a schedule gets claimed it gets assigned a specific user id. Here is where the tricky part comes in. I am trying to pick a user and display schedule times that don't overlap with what they have already been accepted or been scheduled.

Here is what I have so far

SELECT * 
  FROM schedule 
 WHERE user_id = 123456;

That gets me all the ranges of times a person has already accepted

SELECT * 
  FROM schedule 
 WHERE user_id = 0;

This gets me all the schedule rows that are available. I'm not quite sure how to combine them such that the final result is a list of schedule elements whos user_id =0 and the startdate / enddate don't exist between the startdate and endate from the ones already assigned a user.

I would think it would be something like

SELECT * 
  FROM schedule 
 WHERE user_id = 0 
   AND (loop through schedule rows testing for 
           (startdate < loopstartdate and enddate < loopstartdate) || 
           (startdate > loopenddate)

The where is what I'm struggling with. Any ideas? If someone could at least point me in the right direction to what mechanism I'd use to begin to solve this type of problem that would be awesome.

Answer 1

SELECT a.*
FROM schedule a
WHERE user_id = 0
  AND NOT EXISTS (
    SELECT NULL
      FROM schedule b
      WHERE b.user_id = 123456
        AND b.start_date <= a.end_date
        AND b.end_date >= a.start_date
  )