Help building a query

[Kalisto]

Sorry to post this here, I have been trying for a few hours now to construct this (mostly using access to see how things work, then trying out combinations.)

Where I work, we have a number of rooms with different equipment in (Projectors, coffee machines, tables etc)

I am trying to build a simple query to allow the user to say I need a room for 5 hours, Tomorrow, and it must have a projector.

I can build a simple query to get the rooms that have my criteria, such as
"select * from tblRoom where tblRoom.projector=true"

This returns a list of all potential rooms.

How can I modify this the query to list only rooms that match the criteria, but also are not listed in a second table (tblBooking) for the users date and time ?

(The tblBookings table has a foreign key pkRoom that is the Primary key of each room)

I have tried
"SELECT * FROM tblRoom LEFT JOIN tblBookings WHERE (tblRoom.projector=true) AND (tblBooking.fKeyRoom <> tblRoom.pkRoom)"

But this returns me nothing. ( I know that this doesn't include date time as criteria, but initially I would just like to be able to list all rooms with projectors that aren't booked ! (Small steps to begin with))

Any help greatfully recieved.

K

 

[swampBoogie]

SELECT * 
  FROM tblRoom 
 WHERE projector=true
   AND pkRoom not in 
       (select fkeyRoom
          from tblBooking
         where bookingDate = #2004-04-24#)

 

[PruSQLer]

Select tblRoom.*
From tblRoom left outer join tblBookings
ON tblBooking.fKeyRoom = tblRoom.pkRoom
AND bookingDate = '2004-04-23'
Where tblRoom.projector=true
and tblBooking.fKeyRoom is null

 

[Kalisto]

Thanks to Both of you, both your answers work. Now to Expand on that, if they wanted to book a particular room for say 9.00 AM to 12.00AM, how do I structure my syntax to check that the room is not inside a rage of times (or even dates, in case it's a training course).

Sorry if this is a simple question, but I have no books here, and whilst the DB is at present access, we as a department are moving away from it, so I want to keep the solution generic to avoid learning the idiosyncracies of a new system in a few months.

Thanks in Advance,

K

 

[PruSQLer]

Databases often differ quite a bit regarding how they handle dates and times. For instance, I use DB2 which has a BETWEEN function (e.g. Start_dt BETWEEN '2004-1-31' and '2004-4-30') but I don't think Access offers that. So I think you'd have to do:

Select tblRoom.*
From tblRoom left outer join tblBookings
ON tblBooking.fKeyRoom = tblRoom.pkRoom
AND bookingDate > '2004-04-17' and bookingDate <
'2004-4- 23'
AND bookingTime >= '9:00' and bookingTime <= '12:00'
Where tblRoom.projector=true
and tblBooking.fKeyRoom is null

I'm not sure how Access handles times but you can check the HELP Screens. Once you have that formatting worked out, this would work for just about any RDB.