ghosting999 ghosting999 - 1 month ago 17
MySQL Question

Trouble with SQL query using COUNT

I am trying to list the days appearing in the reservation table for which only red
boats are reserved using the COUNT aggregate function and can't seem to get it. This is the query I ran using NOT EXISTS and I was looking for a simple way to convert it to a COUNT aggregation.

SELECT DISTINCT r1.day
FROM reservation r1
WHERE NOT EXISTS (
SELECT r2.day
FROM boat b, reservation r2
WHERE (
b.bname = r2.bname
AND r1.day = r2.day
AND b.color <> "red"));`


Here is the table

sailor table:

sname rating
Brutus 1
Andy 8
Horatio 7
Rusty 8
Bob 1


boat table:

bname color rating
SpeedQueen white 9
Interlake red 8
Marine blue 7
Bay red 3


reservation table:

sname bname day
Andy Interlake Monday
Andy Bay Wednesday
Andy Marine Saturday
Rusty Bay Sunday
Rusty Interlake Wednesday
Rusty Marine Wednesday
Bob Bay Monday

Answer
SELECT r.day 
FROM reservation r
JOIN boat b
  ON r.bname = b.bname
GROUP BY r.day 
HAVING COUNT(CASE WHEN b.color <> 'red' THEN 1 ELSE NULL END) = 0
   AND COUNT(CASE WHEN b.color = 'red' THEN 1 ELSE NULL END) > 0 -- optional
Comments