会议室预订系统SQL查询问题：筛选可用会议室返回无效结果

Hey there! Let's figure out why your query isn't returning the right available meeting rooms. The core issue here is that your current logic isn't correctly identifying overlapping reservations, and your left join approach is being filtered in a way that doesn't preserve all rooms.

First, Let's Fix the Time Overlap Logic #

Two time slots overlap if the existing reservation starts before your target ends and ends after your target starts. This single condition covers all overlap scenarios:

• Your target slot is entirely inside an existing reservation
• Your target slot overlaps the start of an existing reservation
• Your target slot overlaps the end of an existing reservation
• The existing reservation is entirely inside your target slot

Your original queries either miss these cases or filter out rooms incorrectly. Let's fix that.

Solution 1: Using NOT EXISTS (Clean & Readable) #

This query checks for each room that there are no valid (approved or pending) reservations on the target date that overlap with your desired time slot:

SELECT s.*
FROM salle s
WHERE NOT EXISTS (
    SELECT 1
    FROM reservation r
    WHERE r.idSalle = s.idSalle
      AND r.dateReservation = ':dateReservation'
      AND r.etatreserv IN (0, 2) -- Include approved (0) and pending (2) reservations; exclude rejected (1)
      AND r.hDebut < ':hFin' -- Existing reservation starts before target ends
      AND r.hFin > ':hDebut' -- Existing reservation ends after target starts
);

Solution 2: Using LEFT JOIN + Filter for No Conflicts #

If you prefer a join-based approach, this works too. We left join on conflicting reservations, then keep only rooms with no matching conflicts:

SELECT s.*
FROM salle s
LEFT JOIN reservation r
    ON s.idSalle = r.idSalle
    AND r.dateReservation = ':dateReservation'
    AND r.etatreserv IN (0, 2)
    AND r.hDebut < ':hFin'
    AND r.hFin > ':hDebut'
WHERE r.idReservation IS NULL;

Why This Works #

• The NOT EXISTS/LEFT JOIN logic ensures we only keep rooms with no overlapping valid bookings.
• We filter for etatreserv IN (0,2) because rejected reservations (status 1) shouldn't block a room—adjust this if your business logic treats pending reservations differently.
• The time condition covers every possible overlap case, so you won't miss blocked rooms.

Testing Your Scenario #

For your test case (dateReservation = '2019-06-14', hDebut='16:00:00', hFin='17:00:00'):

• Room 1 has a 15:00-18:00 reservation (status 2) that overlaps with your target slot, so it's excluded.
• Room 10 has no matching reservations, so it's correctly included in the results.

内容的提问来源于stack exchange，提问作者HADDADI