dissidianUltima dissidianUltima - 1 year ago 40
MySQL Question

How to SELECT rows from table A that do not match with table B (using time range, based from table B)

Im a student, trying to design a system that can find ALL vacant rooms at a certain time and day, given the schedules of professors based from the existing database of their school.
For each rooms, a schedule is generated from 7:30:00 - 19:30:00 (incremental by 00:30:00) to the table named 'room_schedule'. Each schedule of a room is referenced by room_id from another table named 'room_details'. On the other hand,
the table 'room_details' is linked to table 'prof_schedule' by room. Schedules of professors are recorded on an existing table 'prof_schedule'.

Here's how the tables look like


TABLE 'room_schedule'

day time_start time_end room_id
MWF 07:00:00 07:30:00 36
MWF 07:30:00 08:00:00 36
MWF 08:00:00 08:30:00 36
MWF 08:30:00 09:00:00 36
MWF 09:00:00 09:30:00 36
MWF 09:30:00 10:00:00 36
MWF 07:00:00 07:30:00 37
MWF 07:30:00 08:00:00 37
MWF 08:00:00 08:30:00 37
MWF 08:30:00 09:00:00 37
MWF 09:00:00 09:30:00 37
MWF 09:30:00 10:00:00 37

TABLE 'room_details'
room_id room_name room
36 Biology Room 309
37 Physics Room 307


TABLE 'prof_schedule'
sched_id professor Day room time_start time_end subj
1 Jeffrey Smith MWF 309 8:00:00 9:30:00 Biology
2 Ben Williams MWF 307 8:30:00 10:00:00 Physics


Based from the schedules of professors, i wanted the result for vacant rooms to be like this


RESULT vacant rooms
day time_start time_end room_name
MWF 07:00:00 07:30:00 Biology Room
MWF 07:30:00 08:00:00 Biology Room
MWF 09:30:00 10:00:00 Biology Room
MWF 07:00:00 07:30:00 Physics Room
MWF 07:30:00 08:00:00 Physics Room
MWF 08:00:00 08:30:00 Physics Room


I have tried the query below, but it seems like it removes all records from 'room_schedule' having time_start and time_end from 'prof_schedule'

SELECT day, time_start, time_end, room_name
FROM room_details AS rd
INNER JOIN room_schedule AS rs
ON rd.room_ID=rs.room_ID
WHERE (
(time_start NOT IN(SELECT time_start FROM prof_schedule)) AND
(time_end NOT IN (SELECT time_end FROM prof_schedule)) AND
(rs.day NOT IN (select day from prof_schedule)) AND
(rs.room_id NOT IN (SELECT DISTINCT rd.room_ID from room_details AS rd
INNER JOIN prof_schedule AS ps on rd.room=ps.room))
)


Can someone suggest a query that would show the result I mentioned?

P.S.
The design of the 3rd table (prof_schedule) needs to be retained because they'll be importing these from their existing system to the system I'm designing.

Answer Source

Try something like this and let me know.

SELECT a.day, a.time_start, a.time_end, b.room_name 
FROM `room_schedule` a 
INNER JOIN `room_details` b ON a.room_id = b.room_id
INNER JOIN `prof_schedule` c ON b.room = c.room AND 
IF(a.time_end > c.time_start, a.time_start >= c.time_end, a.time_start < c.time_start)

Try using GROUP BY clause like this

SELECT a.*, b.room_name 
FROM `room_schedule` a 
INNER JOIN `room_details` b ON a.room_id = b.room_id
INNER JOIN `prof_schedule` c ON b.room = c.room AND 
IF(a.time_end > c.time_start, a.time_start >= c.time_end, a.time_start < c.time_start)
GROUP BY a.room_id, a.time_start, a.time_end