Problems with MySQL query

[guillem]

I am developing an online booking system and i am having some problems with a MySQL query.

I have table hotel and hotel_availability
and i want to do this:

SELECT hotel.id, hotel.name FROM hotel,hotel_availability WHERE hotel.id=hotel.availability.id AND hotel.id NOT IN (SELECT hotel_availability.id FROM hotel_availability WHERE date >= 'IN DATE HERE' AND date <= 'OUT DATE HERE' AND free_roms < 'HOW MANY ROOMS CLIENT WANTS');

Any clue on how I can do this without subselects? The second subselect is that way because i have a table where i only insert the booking, so the easiest way to check if some date is not available is to check for all dates between IN DATE and OUT DATE where free_roms is less than ROOMS CLIENT WANTS.

Any help would be greatly appreciated

[edited by: txbakers at 11:57 pm (utc) on June 1, 2004]
[edit reason] removed profanity [/edit]

[yowza]

I'm far from an expert, but I'll try to help. I noticed a couple of errors so I'm not exactly sure what you want to do. I don't believe you need subqueries.

SELECT h.id,h.name,ha.id FROM hotel as h,hotel_availability as ha WHERE h.id=ha.id WHERE ha.date >= 'IN DATE HERE' AND ha.date <= 'OUT DATE HERE' AND ha.free_rooms <= 'HOW MANY ROOMS CLIENT WANTS';

[guillem]

The thing is that in the table availability i only insert the booked dates, so when executing the query (which is suposed to show available hotels between selected dates), what i do is to look if there is any booking made between that dates where the availability is less than wanted. If anything matches that, it means that a booking cannot be made on that room/hotel.
So what i do is go onto hotels table and get names of hotels that do not match the other select (this will be available hotels).

I could just look the other way round, just looking if availability is higher or equal, but then i would need to be sure that all the dates between IN DATE and OUT DATE are available which would be kind of difficult i think.

So the best way seems to do it the first way, but i don't know how to get that exact query done.

I think your query is not doing exactly what i mean. Any other clue?

[txbakers]

I find that when I'm faced with a tough query, drawing pictures helps me.

I'll make simple charts with a list of sample data from one table on the left, and a list of sample data from the other on the right.

I'll draw arrows, make sample results sets, etc. until I find the combination that returns me the correct results.

I'm still not clear on what your end result is, but I think you might be looking for an "outer join" between the two tables.