MySQL Querying database for time periods not already assigned

I have a rather complex-seeming query that will form the basis for an online classroom scheduling tool. My challenge is to develop a method to identify w开发者_C百科hich classes a user is signed up for in the st_schedule table, then deduce from the overall table of classes, st_classes, which other classes are available that don't conflict with the user's current classes.

For example, if a user has an entry in st_schedule assigning them to a class from 8:00am to 9:00am, they would be ineligible for any class whose time fell between 8:00am and 9:00am. A class that ran 7:15am - 8:15am would make the user ineligible. I store the start times and end times of classes in the database separately for comparison purposes. It's important that this be as flexible as possible, so the concept of "blocking" times and assigning times to blocks is not a possibility.

Here are excerpts from the tables:

table st_classes (holds class information)
id
start_time
end_time

table st_schedule (holds schedule information)
id
user_id
class_id

I certainly could do this in a series of loops server-side, but I have to think that there's a MySQL method that can do this type of operation in one fell swoop.

You want to join the two tables together to represent the user's classes, and then find unregistered classes where the start time and end time do not fall between the start and end time of the user's classes.

Something like this. Completely off the cuff and untested:

SELECT
    *
FROM
    st_schedule s
    INNER JOIN st_classes c ON c.id = s.class_id
    INNER JOIN st_classes all_classes 
        ON all_classes.start_time NOT BETWEEN c.start_time AND c.end_time
        AND all_classes.end_time NOT BETWEEN c.start_time AND c.end_time
WHERE
   s.user_id = 1

Edit: Try #2 I only have a moment to look at this. I think I reversed the second join clauses. The all_classes alias represents the full list of classes, where the "c" alias represents the classes that the student is signed up for.

SELECT DISTINCT
    all_classes.*
FROM
    st_schedule s
    INNER JOIN st_classes c ON c.id = s.class_id
    INNER JOIN st_classes all_classes 
        ON c.start_time NOT BETWEEN all_classes.start_time AND all_classes.end_time
        AND c.end_time NOT BETWEEN all_classes.start_time AND all_classes.end_time
WHERE
   s.user_id = 1

This is using table variables in mssql but the sql selects should translate over to mysql

First the sample data

DECLARE @st_classes TABLE
    (
     ID INT NOT NULL,
     Title VARCHAR(40) NOT NULL,
     StartTime DATETIME NOT NULL,
     EndTime DATETIME NOT NULL
    )       

DECLARE @st_schedule TABLE
    (
     ID INT NOT NULL,
     UserID INT NOT NULL,
     ClassID INT NOT NULL
    )       

INSERT INTO @st_classes (ID, Title, StartTime, EndTime)
SELECT 1,'Class1','08:00:00','09:30:00' UNION 
SELECT 2,'Class2','09:30:00','11:30:00' UNION
SELECT 3,'Class3','11:30:00','16:00:00' UNION
SELECT 4,'Class4','16:00:00','17:30:00' UNION
SELECT 5,'Class5','09:00:00','11:45:00' UNION
SELECT 6,'Class6','07:00:00','18:00:00' 

INSERT INTO @st_schedule(ID, UserID, ClassID)
SELECT 1,1,1 UNION
SELECT 2,1,2 UNION
SELECT 3,2,6

Next a bit of sql to confirm the tables join OK (selecting scheduled courses for user with an ID of 1) - Returns class 1 and 2

SELECT *
FROM    @st_schedule AS S INNER JOIN 
        @st_classes AS C ON S.ClassID = C.ID
WHERE  S.UserID = 1 

Now we need to select all the ID of the courses where they overlap time wise with the users scheduled ones (including the scheduled ones) - Returns 1,2,5,6

SELECT  AC.ID
FROM    @st_classes AS AC
        INNER JOIN ( SELECT C.StartTime,
                            C.EndTime
                     FROM   @st_schedule AS S
                            INNER JOIN @st_classes AS C ON S.ClassID = C.ID
                     WHERE  S.UserID = 1
                   ) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
                                AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
                              )
GROUP BY AC.ID 

Now we need to select all courses where the Course ID is not in our list of overlapping course IDs. - Returns course 3 and 4

SELECT  *
FROM    @st_classes
WHERE   ID NOT IN (
        SELECT  AC.ID
        FROM    @st_classes AS AC
                INNER JOIN ( SELECT C.StartTime,
                                    C.EndTime
                             FROM   @st_schedule AS S
                                    INNER JOIN @st_classes AS C ON S.ClassID = C.ID
                             WHERE  S.UserID = 1
                           ) AS UC ON ( AC.StartTime < DATEADD(ss, -1, UC.EndTime)
                                        AND DATEADD(ss, -1, UC.EndTime) > UC.StartTime
                                      )
        GROUP BY AC.ID )

Change the user ID filter to 2 and you should not get any returned as the course assigned to that user overlaps all courses.