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')";
Mysql statement help
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Mysql rut
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?
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
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'";
$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'";