Query problem

[aos]

Hi!
I want the query to return the rooms available for EACH date between the 2 selected dates. At the moment, the query is only returning the common room that is available for each date (even though each date may have other rooms available). Any ideas - thanks!

sSQL = "SELECT [ListDates].[Date], Room.RoomNo From Room, ListDates WHERE (((Room.RoomNo) Not In (SELECT [ListDates].[RoomNo] FROM ListDates WHERE ((([ListDates].[Date]) Between #" & firstdate & "# And #" & seconddate & "#)))));"

 

[ija]

Hi.

This is quite difficult. Try the minus operator:

-- all combinations
select ld.date, r.roomno
from Room r, ListDates ld
where ld.date between firstdate and seconddate
minus
-- booked combinations
select ld.date, r.roomno
from Room r, ListDates ld
where ld.date between firstdate and seconddate
and ld.roomno = r.roomno;

Expect performance problems when the tables get large.
Sorry for syntax - I'm more used to oracle...