SELECT查询结果不符预期求助：两个SQL查询存在问题

Let's break down and fix each of your problematic queries one by one.

First Query: Finding Available Slots on a Specific Date #

The Problem #

Your original query's NOT EXISTS clause is checking for any bookings on '2018-01-27' instead of verifying if the current slot is booked on that date. This inverted, unlinked logic means it returns slots even when all slots are booked for the day—exactly the opposite of what you need.

Corrected Query #

We need to tie the subquery directly to the outer slot to check if it's been booked on the target date:

SELECT s.id, s.day_of_week, s.title 
FROM slots s 
WHERE s.service_id = 3 
  AND s.day_of_week = DAYOFWEEK('2018-01-27')
  AND NOT EXISTS (
    SELECT 1 
    FROM bookings_has_slots bhs
    JOIN bookings b ON bhs.booking_id = b.id
    WHERE bhs.slot_id = s.id
      AND b.date = '2018-01-27'
);

Why This Works #

• We first filter slots to only those for service 3 and the correct weekday.
• The NOT EXISTS subquery now checks if the specific slot from the outer query has a booking on '2018-01-27'. Since all three slots are booked, this query returns no results as expected.

Second Query: Finding Dates Where All Slots for the Service Are Booked #

The Problem #

Your nested NOT EXISTS clauses don't properly compare the total number of slots for a weekday against the number of booked slots for each date. This leads to including dates like '2018-02-03' where some slots are still available.

Corrected Query #

We'll use aggregation to count booked slots and compare them to the total slots for the corresponding weekday:

SELECT b.date AS unavailable_date
FROM bookings b
JOIN bookings_has_slots bhs ON bhs.booking_id = b.id
JOIN slots s ON s.id = bhs.slot_id
WHERE s.service_id = 3
GROUP BY b.date
HAVING COUNT(DISTINCT s.id) = (
  SELECT COUNT(*) 
  FROM slots s_total
  WHERE s_total.service_id = 3
    AND s_total.day_of_week = DAYOFWEEK(b.date)
);

Why This Works #

• We group bookings by date and count how many distinct slots are booked for service 3 on that date.
• The subquery fetches the total number of slots for service 3 that match the weekday of the current date.
• The HAVING clause only keeps dates where booked slots equal total slots—meaning all slots are taken. This excludes '2018-02-03' since not all slots are booked there.

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