SQL Query between 3 tables

Question

I have 3 tables (note this may not be the best sql db design)

Room: TypeName, RoomNumber
RoomType: TypeName, ...
Reservation: StartDate, EndDate, TypeName, RoomNumber

My input parameters are startdate and enddate. I'd like to know the distinct roomtypes available. From my understanding the solution goes like this: AvailableRoomTypes = AllRoomTypes - UnavailableRoomTypes

Obviously AllRoomTypes can be found by: SELECT DISTINCT(TypeName) FROM Room; and I know how to get unavailable rooms, but not unavailable roomtypes. Nor do I know how to subtract one set from another. I can get a set of all rooms+roomtypes and another set of unavailable rooms+roomtypes but how do I join them such that it is A-B? Maybe some sort of NOT_CONTAINS function?

I'd like my output parameters to be SELECT * FROM RoomType (for the appropriate roomtypes).

Answer 1

SELECT t.*
  FROM ROOMTYPE t
 WHERE NOT EXISTS(SELECT NULL 
                    FROM RESERVATION r
                    JOIN ROOM ro ON ro.roomnumber = r.roomnumber
                   WHERE ro.typename = t.typename
                     AND r.startdate BETWEEN ?IN_START_DATE AND ?IN_END_DATE
                     AND r.enddate BETWEEN ?IN_START_DATE AND ?IN_END_DATE)

Answer 2

unused RoomTypes

select RT.* 
from RoomType RT
where RT.TypeName not in (
   Select RM.TypeName 
   from Room RM
)

one way to do A-B selection is "not in (select ..)" operator, not exists (select ..) is another and there will be more, I am certain.

Answer 3

You don't want to use a UNION - SUM(reservation.roomtype) and SUM(service.servicetype) are different attributes. But if you did, you'd want to use NULLs to offset the queries so the columns don't overlap. IE:

SELECT SUM(reservation.roomtype), NULL ...
UNION ALL
SELECT NULL, SUM(service.servicetype) ...

Based on the logic you provided, this is the query I came up with:

SELECT SUM(r.roomtype),
       SUM(s.servicetype)
  FROM RESERVATION r
  JOIN BILL b ON b.reservationid = r.reservationid
             AND b.paid = 1
  JOIN SERVICE s ON s.serviceid = b.serviceid

That will return a single row. We'd need to know what the query (or queries depending on the information) group on in order to return a more meaningful resultset. The details of the RESERVATION and SERVICE tables would help us be able to help you.