Page 1 of 1
Mysql statement help
Posted: Wed Apr 09, 2003 10:27 am
by Deddog
I have one table with a description of rooms (“rooms”). The second table is to hold date and details that a room has been booked for. (“booked”)
I want to list all the rooms for an inputted date that are not booked. This is my poor attempt, please help.
"select * from rooms r where r.Available = 'Yes' && r.RoomsId NOT LIKE (Select RoomsId from booked b where b.BookedDate = '$TheDate')";
Posted: Wed Apr 09, 2003 10:56 am
by twigletmac
Which version of MySQL are you using? Version 3 does not support sub-queries.
Mac
Posted: Wed Apr 09, 2003 12:24 pm
by BDKR
Perhaps the below will work.
SELECT * FROM rooms, booked
WHERE rooms.Available = 'Yes'
AND rooms.RoomsId = booked.RoomsId
AND booked.BookedDate != $TheDate
I wasn't 100% sure what you were after. I think this may be close.
Cheers,
BDKR
Mysql rut
Posted: Thu Apr 10, 2003 3:17 am
by Deddog
Yep i'm using version 3.
select * from rooms r, booked b where r.Available = 'Yes' && (r.RoomsId NOT LIKE b.RoomsId AND b.BookedDate = '$TheDate');
Have tried the above sql but it returns no results. There are no rooms booked on the date i specify, so it should list list all the rooms in the rooms table. I don't actuly want to retrieve any data from the booked table, so do i need some sort of JOIN going on?
inspiration - I hope
Posted: Thu Apr 10, 2003 8:14 am
by Deddog
Well after a bit of reading and a pint of stella i might just of cracked it
$sqlAM = "SELECT rooms.* FROM rooms LEFT JOIN booked ON rooms.RoomsId=booked.RoomsId and booked.BookedDate ='$TheDate' and booked.BookedTime='AM'
WHERE booked.RoomsId IS NULL and rooms.Available = 'Yes'";