Wrecking my head!

Orlie

This is wrecking my head...

I have two tables.

RoomFile(RoomName,RoomType)
RoomAvailabilityFile(RoomName,InDate,OutDate)

RoomAvailabilityFile stores the bookings for a particular InDate and OutDate. I want to display the RoomName & RoomType which are NOT in RoomAvailabilityFile on a particular date.

I've tried lots of SQL queries but they just won't work...

Using VB6 with Access.

rs

You need to left join RoomFile with RoomAvailabilityFile and take those rows that have In_Date = Null

join syntax varies from database to database, as it's not standard sql

I've only used mysql + oracle, so I don't know the access equiv

good luck

rs

select RoomFile.RoomName, RoomFile.RoomType from RoomFile left join ( RoomAvailabilityFile on RoomFile.RoomName = RoomAvailabilityFile.RoomName) where In_Date = NULL;

something like that.

feakle

use:
select RoomName,RoomType from RoomFile where RoomName not in (select RoomName from RoomAvailabilityFile where InDate => "your date" and OutDate <= "yourdate")

you might want to check the where clause but the sub query is the best way to go if the table is not huge (ig gets slow after a while).

tommycahir

did you try doing the query in the design mode in access then viewing the results in sql mode then copy the query into the vb editor ... always used to work for me.. ww)

bonkey

Originally posted by rs
select RoomFile.RoomName, RoomFile.RoomType from RoomFile left join ( RoomAvailabilityFile on RoomFile.RoomName = RoomAvailabilityFile.RoomName) where In_Date = NULL;

something like that.

Yeah, thats how I'd do it, but I'd change it just slightly to make the WHERE clause filter on the same field as the JOIN was performed.....because it rules out the case where you have a record in RoomAvailabilityFile with an In_Date of NULL.

Oh, and although some databases will let you get away with it, you should never use '= NULL', if 'IS NULL' is available. Technically, nothing equals a null, not even another null.

As approaches go, I believe it is far more efficient than the more commonly used NOT IN or NOT EXISTS approaches....or at least, it is far more efficient unless you make a mess of setting up your indexes.

jc