query in query

[craigalways]

I have 3 queries need help combining the last one. its for my hotel booking system.

so far I have this is for the date ranges
SELECT roomno FROM booking WHERE 2007 -01 -01 <= enddate AND startdate >=2007 -01 -01

and then this to check to see if the room is already booked.
SELECT DISTINCT roomno FROM room WHERE NOT EXISTS ( SELECT * FROM booking WHERE booking.roomno = room.roomno )

I am trying to output a list of room numbers with roomno as the heading so my while loop can pick it up in php.

I would also like to be able to input start date, end date and room type variables.


Here is a list of tables and fields.

Booking

  bookingno               
  customerno               
  roomno               
  startdate     
  enddate

room

  roomno           
  roomtypeID             
  bath               
  shower             
  teacoffee         
  other

roomtype

  roomtypeID         
  roomtype               
  peakrate             
  offpeakrate


Any help at all would be appreciated I have spent days working on it now.
Thanks Craig.

[hvle]

[quote]
I am trying to output a list of room numbers with roomno as the heading so my while loop can pick it up in php.
[/quote]

[code]
select roomno from room;
// do your php work here to display the results
[/code]

it is that simple, unless you wanted something else.

[craigalways]

i don't think I made that clear the list of roomno needs to based on all 3 of the above queries.

[howster]

I will be glad to help you once you submit a more polite post.

[img]http://www.waltonhall-ilt.org.uk/MoreDetails/images/Calc.gif[/img]

[steelmanronald06]

Here is how I would do it, assuming I follow you:

First add a field to room called booked.  This will be updated.  if it is booked then it will have a 1, if it isn't booked it will have a 0.  Then do

SELECT * FROM room WHERE booked = 0

That way you don't even mess with the Booking table unless you want to see the booked rooms (this way you can see the free rooms)

Okay, then it gets tricky.  Do a loop and inside this loop have a mysql statement that does:

SELECT * FROM roomtype WHERE roomtypeID = $room['roomtypeID']

So it might look something like this:

[code]

$query = "SELECT * FROM room WHERE booked=0";
$execute = mysql_query($query);

while($room=mysql_fetch_array($execute)) {

$q = "SELECT * FROM roomtype WHERE roomtypeID = $room['roomtypeID']";
$row = mysql_fetch_row($result);

echo $row[0]; // zero because it will only return one result per loop

}
[/code]

Hope that helps.  I haven't tested it, but it should work.

[hvle]

Why don't you explain in English, what you wanted the result to be, instead of posting your queries.
I think everyone here pretty familiar with a hotel booking system.

[craigalways]

That would mean i would have to create 300 records for each each day. i was hoping i wouldnt have to go down that route.  craig

Because i am dyslexic and didnt have anybody to proofread it for me.  craig

[hvle]

You do not have to create any records at all.  You posted the 3 table structures, and now we need to know what you wanted out of those 3 tables.  like, "I like to retrieve all available rooms from date 1 to date 2.  Something like this would make more sense.  Know what I meant?

[craigalways]

I need a query that outputs a list of rooms that arnt booked between to dates ranges and have a specific numerical roomtype.

[hvle]

You can achieve this query with both sub-query and right join

These are your search criteria variables:
$my_roomtype :  The specific numerical roomtype
$my_startdate : The start date
$my_enddate :  The end date

first we need to select all rooms that are booked during that my_startdate and my_enddate an:

"select roomno from booking where (startdate between '$my_startdate' and '$my_enddate') or (enddate between '$my_startdate' and '$my_enddate') or (startdate > '$my_startdate' and enddate < '$my_enddate')";

now, we will select all rooms that not belong to any of the room above:
"select roomno from room where roomno NOT IN (select roomno from booking where (startdate between '$my_startdate' and '$my_enddate') or (enddate between '$my_startdate' and '$my_enddate') or (startdate > '$my_startdate' and enddate < '$my_enddate'))";

the query above is not what you wanted because it does not filter out roomtype, so we need a join:

Final query:
"select roomno from room where roomno NOT IN (select roomno from booking where (startdate between '$my_startdate' and '$my_enddate') or (enddate between '$my_startdate' and '$my_enddate') or (startdate > '$my_startdate' and enddate < '$my_enddate')) right join roomtype.roomtype on (roomtype.roomtype = $my_roomtype);

Note: for this to work, you need to use appropriate datatype for start and end date. Use date, datetime or unix time stamp would be good.  Date is the best cuz you really don't need the hour/min in this case.  If you use plain text as your date, it won't work.
roomtype must some kind of number format too.

If you use this query and it won't work, post the error here, because I just type it on top of my head without testing.

Good Luck

[howster]

[img]http://img228.imageshack.us/img228/445/noobtopiacu1.png[/img]
I thought this was relevant. It's the key to the city of Noobtopia. Population: you

[hvle]

thanks, I can live with that.

[craigalways]

It work fine up until the right join
[code]
SQL query: 

SELECT roomno
FROM room
WHERE roomno NOT
IN (


SELECT roomno
FROM booking
WHERE (
startdate
BETWEEN '2007-01-01'
AND '2007-01-01'
)
OR (
enddate
BETWEEN '2007-01-01'
AND '2007-01-01'
)
OR (
startdate > '2007-01-01'
AND enddate < '2007-01-01'
)
)
RIGHT JOIN roomtype.roomtype ON ( roomtype.roomtype = '1' )
LIMIT 0 , 30

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'right join roomtype . roomtype on ( roomtype . roomtype = '1' ) 
LIMIT 0, 30' at line 1
[/code]

The first part works fine thanks.

[JayBachatero]

You need to have the RIGHT JOIN after the FROM.

[craigalways]

[code]
SELECT roomno
FROM room
RIGHT JOIN roomtypeID.room ON ( roomtypeID.roomtype = '1' )
WHERE roomno NOT
IN (


SELECT roomno
FROM booking
WHERE (
startdate
BETWEEN '2007-01-01'
AND '2007-01-01'
)
OR (
enddate
BETWEEN '2007-01-01'
AND '2007-01-01'
)
OR (
startdate > '2007-01-01'
AND enddate < '2007-01-01'
)
)
LIMIT 0 , 30

MySQL said: 

#1142 - SELECT command denied to user 'craig'@'localhost' for table 'room' [/code]
still not sure about the names of the tables and to fields on the join. Any ideas?

[hvle]

i made a mistake on my query at

...right join roomtype.roomtype on ....
should be:
... right join roomtype on ...

[fenway]

[quote author=JayBachatero link=topic=122786.msg508629#msg508629 date=1169221446]
You need to have the RIGHT JOIN after the FROM.
[/quote]
Shudder... flip it around and use LEFT JOIN like the rest of the universe.

[hvle]

what's wrong with right join or join? fenway

[fenway]

RIGHT JOIN just isn't standard, and since it's equivalent to the LEFT JOIN with the table order reversed, it's better to use the latter.

[hvle]

i thought so.