complicated mySQL query

[ambrennan]

Hi all

I am trying to write a query which will search the database for whatever rooms are available on the dates selected (arrival and departure ) which involes querying different tables. I'm new to php and mysql and really struggling with this - The code below is my feeble attempt - Can you help
$query = "SELECT room_number, room_description FROM room_desc_t,
WHERE
room_type = $RoomType, room_smoking = $SmokingYesNo, AND
room_number NOT IN
SELECT res_room_number from reservations_t " WHERE
res_arrival_date <= $arrivalday, res_depart_date > $departuredate
SELECT res_room_number from reservations_t WHERE res_arrival_date >= $arrivalday AND res_arrival_date < $departuredate"
$result=mysql_query ($query) or die ("Unable to execute query: ".mysql_error()); //(mysql_error());

[Guest footballkid4]

You can try this:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]`room_desc_t`[/color] t [color=green]LEFT[/color] [color=green]JOIN[/color] `reservations_t` r [color=green]WHERE[/color] t.room_type[color=orange]=[/color][color=red]'{$RoomType}'[/color] [color=blue]AND[/color] room_smoking[color=orange]=[/color][color=red]'{$SmokingYesNo}'[/color] [color=blue]AND[/color] r.res_arrival_date [color=orange]>[/color][color=orange]=[/color] [color=red]'{$Arrival}'[/color] [color=blue]AND[/color] r.res_departure_date [color=orange]<[/color] [color=red]'{$Departure}'[/color] [!--sql2--][/div][!--sql3--]

That should work, but it's untested and not guaranteed.