-
set difference query
Im working on some practice questions for my oracle class and one is asking me to list all the hotel rooms that are vacant on june 4th 2006. i know i need to get all rooms and then subtract all the rooms booked on that date to return the vacant ones however i am having trouble getting the SQL right. could someone please help me out.
here is my table structrue
Hotel(hotel_no, name, address)
Room(room_no, hotel_no, type, price)
booking(hotel_no, guest_no, room_no, date_from, date_to)
i need to select hotel_no, room_no, date_from, and date_to
again this is prob pretty simple but i just started learning this after switching from access.
thanks
alex
-
almost solved
so i got it figured out but my last question is how to i get the date_to and date_from to show up as blank. here is my code so far:
select r.hotel_no, r.room_no, date_from, date_to
from room r, booking b
where r.hotel_no = b.hotel_no(+)
minus
select r.hotel_no, r.room_no, date_from, date_to
from room r, booking b
where r.hotel_no = b.hotel_no(+)
and date_from<= '04-Jun-06'
and date_to>= '04-Jun-06';
it returns all the rooms that were not booked on june 4th. but when it displays the results it lists the date_from and date_to. i would like it to leave this information out but still have the columns. any suggestions?
here is my output:
HOTEL_NO ROOM_NO DATE_FROM DATE_TO
10 101 02-MAY-06 20-MAY-06
10 102 02-MAY-06 20-MAY-06
10 103 02-MAY-06 20-MAY-06
20 101 01-MAY-06 03-MAY-06
20 101 20-MAY-06 31-MAY-06
20 101 20-JUN-06 23-JUN-06
20 102 01-MAY-06 03-MAY-06
20 102 20-MAY-06 31-MAY-06
20 102 20-JUN-06 23-JUN-06
20 106 01-MAY-06 03-MAY-06
20 106 20-MAY-06 31-MAY-06
20 106 20-JUN-06 23-JUN-06
30 201 10-JUN-06 15-JUN-06
30 201 21-JUN-06 23-JUN-06
30 211 10-JUN-06 15-JUN-06
30 211 21-JUN-06 23-JUN-06
40 120 25-JUN-06 26-JUN-06
40 120 25-JUN-06 27-JUN-06
40 121 25-JUN-06 26-JUN-06
40 121 25-JUN-06 27-JUN-06
40 122 25-JUN-06 26-JUN-06
40 122 25-JUN-06 27-JUN-06
-
I would think this would work. In pseudo code...
(all rooms)
minus
(all rooms booked on 04-jun-06)
so this is...
(select hotel_no, room_no
from room)
minus
(select hotel_no, room_no
from booking
where 04-Jun-2006 between date_from and date_to)
The only question I have - is "between" inclusive or exclusive? I'm not sure but you should be able to test for it. Let me know how you get on.
