Help With Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help With Access

Author

Topic

SamHarnew
Starting Member

14 Posts

Posted - 2004-05-02 : 18:49:44

I have a table with all the rooms at a lesure centre (the rooms are just numbers). I also have a table with all the rooms being used at the lesure centre (these are also numbers, they corrospond to the numbers on the rooms table). what i need is a query that gives me the avalible rooms. i have not got a clue how to do this and anyhelp would be appreiated, thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-02 : 21:18:24

select r.*
from rooms r
left join used u
on r.room_id = u.room_id
where u.room_id is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-02 : 22:11:02

This isn't any better than nr's query, just different.

SELECT *
  FROM rooms r
  WHERE r.room_id NOT IN (SELECT room_id FROM used)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 00:56:46

But more comprehensible if you haven't come across the other techniqu - it's worth learning though.
I think better than the in clause but not as good as the left outer join is

SELECT *
FROM rooms r
WHERE not exists (select * from used u where u.room_id = r.room_id)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamHarnew
Starting Member

14 Posts

Posted - 2004-05-03 : 05:42:09

thanks for all of your help but i dont really understand it. if i give you all of the feild names and table names is it possible to show the the exact peice of SQL needed, thanks. ok i have a table called tblSquashCourts and a table called tblBookedCourts, both tables have just the feild 'court' ,thanks agin for any help in advanced.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 05:55:38

select r.*
from tblSquashCourts r
left join tblBookedCourts u
on r.court = u.court
where u.court is null

SELECT *
FROM tblSquashCourts r
WHERE r.court NOT IN (SELECT court FROM tblBookedCourts)

SELECT *
FROM tblSquashCourts r
WHERE not exists (select * from tblBookedCourts u where u.court = r.court)

But don't you worry about the booking times?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamHarnew
Starting Member

14 Posts

Posted - 2004-05-03 : 06:01:52

yeh, but the tbl that displays the used courts is actully a query (sorry i forgot about that), im using the SQL code with combo boxes so u can seect a time then it will return the avalible courts in a list box

SamHarnew
Starting Member

14 Posts

Posted - 2004-05-03 : 06:03:15

o yeh i forgot to say, the query has already nerrowed it down to the right sport, time, and date

SamHarnew
Starting Member

14 Posts

Posted - 2004-05-03 : 06:07:02

thanx to everyone that helped, it works

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-03 : 09:50:22

quote:
Originally posted by nr

select r.*
from tblSquashCourts r
left join tblBookedCourts u
on r.court = u.court
where u.court is null

SELECT *
FROM tblSquashCourts r
WHERE r.court NOT IN (SELECT court FROM tblBookedCourts)

SELECT *
FROM tblSquashCourts r
WHERE not exists (select * from tblBookedCourts u where u.court = r.court)

Isn't the 3rd query going to require repeated execution for every row?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources