Posted by Buzby on 11/08/80 11:33

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns971EEB66FBC0AYazorman@127.0.0.1...
> Buzby (gb@pumpupthe.net) writes:
>> I have two tables - RoomTypes and Availability. The query below brings
>> back the information I need - except I need to filter it one more time
>> and exclude any room that has a 0 in a field call AvailDayShow (in the
>> Availability table) in it across a selected date range. (It's basically
>> looking across a date range and if a room value is 0 for AvailDayShow in
>> this range the rooms not bookable so don't show it)
>>
>> SELECT DISTINCT
>> RoomTypes.RoomID,
>> RoomTypes.RoomHotelID,
>> RoomTypes.RoomType,
>> RoomTypes.RoomCode,
>> RoomTypes.RoomDescription,
>> RoomTypes.RoomIncludes,
>> Availability.*
>> FROM
>> (RoomTypes
>> INNER JOIN Availability ON
>> RoomTypes.RoomID = Availability.AvailRoomID)
>>
>> Where (Availability.AvailDate between #12/09/2005# AND #12/13/2005#)
>
> Add
>
> AND NOT EXISTS
> (SELECT *
> FROM Availability A2
> WHERE A.AvailRoomID = A2.AvailRoomId
> AND A2.AvailDate between #12/09/2005# AND #12/13/2005#
> AND A2.AvailDayShow = 0)

Erland

Thanks so much for the example - I now fully understand it and am away with
it!

Kind regards

Grant

• Next in forum: Re: Indexing and Queries
• Prev in forum: Re: Why upgrade to MS SQL Server (be it 2000 or 2005) from Access (say, 97)?
• Thread view: Re: Newbie:Not Exists?

[Reply to this message]