using info from 1 query for another query

jsinker

Hi I am trying to make a on line reservation system for villas, what I need to do is show which villas are not booked when the user enters a start and end date so far i have 2 querys one checks the dates and shows the villas that are booked for the dates the second does a left join and links the results to show a null record if the villa is booked however I do not know how to link the 2 queries. here is the code for the 1st query.
this is recordset 1
SELECT room
FROM bookings
WHERE startdate BETWEEN 'start' AND 'end' OR enddate BETWEEN 'start' AND 'end'

this is record set 2
SELECT *
FROM homes LEFT JOIN bookings ON homes.room = bookings.room
WHERE bookings.room IS NULL

Please can someone help!!!

Lars_Berg

SELECT *
FROM homes LEFT JOIN bookings
ON homes.room = bookings.room
and ( startdate BETWEEN 'start' AND 'end'
OR enddate BETWEEN 'start' AND 'end'
or ('start' <= startdate and enddate <= 'end'))

WHERE bookings.room IS NULL

jsinker

Thanks Lars, that works great the only item I forgot to include is I want to be able to search on room size as well as date. my table bookings has a colum roomtype which lists 2 3 4 5 6 bedroom from my search page I have a drop down menu that lists the room size. this info is sent to the script along with the dates start and end is the a simple piece of code that can be included to the query you sent me to enable this search feature?

Lars_Berg

Shouldn't be any problem adding that criteria as well.

e.g. pseudo

SELECT *
FROM homes LEFT JOIN bookings
ON homes.room = bookings.room
and ( startdate BETWEEN 'start' AND 'end'
OR enddate BETWEEN 'start' AND 'end'
or ('start' <= startdate and enddate <= 'end'))
and roomtype = $rooms
WHERE bookings.room IS NULL