dissidianUltima dissidianUltima - 4 months ago 5
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

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
Comments