Mysql statement help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Mysql statement help

Post 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')";
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Which version of MySQL are you using? Version 3 does not support sub-queries.

Mac
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post 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
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

Mysql rut

Post 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?
Deddog
Forum Commoner
Posts: 55
Joined: Thu Sep 26, 2002 6:05 am
Location: Brighton

inspiration - I hope

Post 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'";
Post Reply