SQL help please!

[judas]

Don't know if it's the heat or what, but I can't get my head around this.

Should be very simple - here's the table structure:



What I need is a query that returns, for any given date, the full list of rooms from tblrooms and a count of bookings for that day ie:

Name | Count
-------------
Room1 | 0
Room2 | 1
Room3 | 0

Can this be done in a single MySQL query? I've tried all kinds of combination of joins and is Null checks, but nothing seems to be working

[Plotloss]

Surely you need a view in Booking Date order?

[Don]

Have you tried something like this?


SELECT R.ROOMNAME, COUNT(*)
FROM tblBookings B
INNER JOIN tblRooms R on R.RoomID = B.RoomID
WHERE BookingDate = '01-SEP-2006'
GROUP BY ROOMNAME


Sorry - just worked out you want records that have no bookings....errrm
SELECT R.ROOMNAME,
(SELECT COUNT(*) FROM tblBookings B
WHERE B.RoomID = R.RoomID
AND B.BookingDate = '01-SEP-2006') AS BOOKINGCOUNT
FROM tblRooms R

Edited by Don on Wednesday 5th July 11:30

[judas]

Don, that's pretty similar to the query I ended up with and gives the same results, ie returns only the names of rooms with bookings, but not the names of those without corresponding bookings.

What it needs to do is return all room names regardless of whether there's a corresponding record in the bookings table (for the given date).

Edited to add: Don - you're a star! That second one appears to do the trick! Muchos gracias!

It's for a B&B so potential guests can see see the room availability btw

Edited by judas on Wednesday 5th July 11:37

[pdV6]

I think you're over-complicating the issue. Given the scenario and the fact that a room can only be booked once on a given date, surely all you want is an availability list, i.e.

SELECT RoomName FROM tblRooms
WHERE RoomID NOT IN
(SELECT RoomID FROM tblBookings WHERE BookingDate='01-SEP-2006')

[sp60]

select r.RoomName, count( t.BookingID ) as BookingsCount
from tblRooms r
left outer join tblBookings b on r.RoomID = t.RoomID
where t.BookingDate = <A date>
group by r.RoomName, t.BookingID

Never really used MySQL, does it have a date field type or is it DateTime?
If it's just date you will be fine, if it's datetime you will have to check whether an = comparator includes the timestamp, if it does you will probably have to trim the timestamp out.


> didn't see that this had been sorted, oh well, now have a few ways of doing it!!

Edited by sp60 on Wednesday 5th July 12:01

[judas]

pdV6 said:

I think you're over-complicating the issue. Given the scenario and the fact that a room can only be booked once on a given date, surely all you want is an availability list, i.e.

SELECT RoomName FROM tblRooms
WHERE RoomID NOT IN
(SELECT RoomID FROM tblBookings WHERE BookingDate='01-SEP-2006')

Yep - except that's not what the client wants

[sp60]

judas said:

Yep - except that's not what the client wants

Clients, pah, they know nothing, give them what they realy want, not what they ask for, your the professional!



If only that could happen, my life would be so much easier!!

[pdV6]

[judas]

The benefit of doing it this way is that it makes it easier to administer the system. If an admin is logged in then the room names in the calendar become links that toggle the status to booked/unbooked with very little extra work - which is always a bonus!

[Don]

judas said:

Edited to add: Don - you're a star! That second one appears to do the trick! Muchos gracias!

No problem. SQL is specialty of mine...