Svein Erling Tysvær wrote:

> this will not do what he wanted for two reasons:
>
> 1) This query only counts the rooms of which no reservation or checkin
> exists. He probably wants returned 1 if there's two rooms of that room_type
> and one is reserved, but your query doesn't cover that.

Shame on me! You're right! My only excuse is morning time here in North America
- I had pretty hard day yesterday and not enough sleep. OK, next attempt:
SELECT A.Room_Type, COUNT(A.Room_Type) - NOT_AVAILIABLE AS AVAILIABLE
FROM Room A,
(
SELECT Room_Type, RESERVED + CHECKED AS NOT_AVAILIABLE
FROM
(
SELECT Room_Type, COUNT(*) AS RESERVED, O AS CHECKED
FROM Reservation
WHERE Arrival_Date >= :A_Date AND Arrival_Date < :D_Date
OR Departure_Date >= :A_Date AND Departure_Date < :D_Date
GROUP BY Room_Type
UNION
SELECT Room_Type, 0AS RESERVED, COUNT(*) AS CHECKED
FROM Check_In
WHERE Check_In_Date >= :A_Date AND Check_In_Date < :D_Date
OR Departure_Date >= :A_Date AND Departure_Date < :D_Date
GROUP BY Room_Type
)
) B
WHERE A.Room_Type = B.Room_Type
AND A.Room_Type = :Room_Type