montymoose
Posts: 85
|
| Posted: 07/27/2007, 5:31 AM |
|
Hi,
I wonder if anyone could shed any light on this:
I have a simple database with 2 tables in it. 'Rooms' and 'Bookings' each room can have multiple bookings. I can easily show all the bookings for a certain room. But how do I return a list of all rooms with no bookings. IE records in the Rooms table which have no child records on the bookings table.
Any thoughts?
John
|
 |
 |
DonB
|
| Posted: 07/27/2007, 5:57 AM |
|
That's a simple SQL 'subtract' operation:
SELECT * FROM rooms where NOT EXISTS (SELECT * FROM bookings WHERE
bookings.roomid = rooms.roomid) ;
(from the set of all rooms, subtract the set of rooms that have a booking)
You probably want to refine that with a date range or other qualification,
as rooms that were booked in the past shouldn't influence those booked in
the future!
--
DonB
http://www.gotodon.com/ccbth
"montymoose" <montymoose@forum.codecharge> wrote in message
news:546a9e5bb7012d@news.codecharge.com...
> Hi,
>
> I wonder if anyone could shed any light on this:
>
> I have a simple database with 2 tables in it. 'Rooms' and 'Bookings' each
room
> can have multiple bookings. I can easily show all the bookings for a
certain
> room. But how do I return a list of all rooms with no bookings. IE records
in
> the Rooms table which have no child records on the bookings table.
>
> Any thoughts?
>
> John
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Wkempees
|
| Posted: 07/27/2007, 6:05 AM |
|
depends on your join statement.
select Rooms.*, Booking.*
from Rooms
left join Bookings on Bookings.room_id = Rooms.id
Will result in all bookings for all rooms.
select Rooms.*, Booking.*
from Rooms
left join Bookings on Bookings.room_id = Rooms.id
where Bookings.room_id is NULL
Will result in all rooms with no bookings.
field name room_id and id are assumptions, replace with yours
Walter
|
|
|
|
ckroon
Posts: 869
|
| Posted: 07/29/2007, 2:19 PM |
|
Thanks Walter, that answered my question as well. I had a similar setup with a teachers class list and a test score database. Prefect!
_________________
Walter Kempees...you are dearly missed. |
|
|
|
|
