MySQL Selecting non-unique records based on date

Hi,

I have a simple “bookings/reservation” MySQL database table (tblbookings). There are four fields in the table:

bookId (auto increment unique ID)
bookdate (Date - YYYY-MM-DD)
bookrm (INT) (this is the room number booked on that date)
bookorderId (this is a relational field that lists the full booking/order details in another table).

Typically someone may book a particular room for 3 nights and another user may book a different room for some other nights, so the table data would look like this:

bookId - bookdate - bookrm - bookorderId
1000 - 2015-08-21 - 1 - 564
1001 - 2015-08-22 - 1 - 564
1002 - 2015-08-23 - 1 - 564
1003 - 2015-08-22 - 2 - 565
1004 - 2015-08-23 - 2 - 565
1005 - 2015-08-24 - 2 - 565

This all works fine and I can use it to check whether a room is available on a certain day (i.e. if it’s not in the table).

However due to there being another (not mine) way of adding records occasionally a double booking may occur - where the same room (bookrm) is listed for more than one order (bookorderId) on a particular date (bookdate).

Therefore I’m trying to create an alert for the administrator that will query the table to check whether any rooms are listed more than once on the same day - i.e.

bookId - bookdate - bookrm - bookorderId
1000 - 2015-08-21 - 1 - 564
1001 - 2015-08-22 - 1 - 564
1002 - 2015-08-23 - 1 - 564
1003 - 2015-08-23 - 1 - 565
1004 - 2015-08-24 - 1 - 565
1005 - 2015-08-25 - 1 - 565

The result would be a recorsset that gave me the bookorderId, bookdate and bookrm of the clashed bookings (only one side of the clash would need to be outputted if that’s easier). To be honest I haven’t a clue how to construct a SELECT statement that can do this - can anyone help?

Many thanks
Scim

Something like this should work

SELECT bookId, bookdate, bookrm, bookorderId, COUNT(bookrm) rooms
FROM tblbookings 
GROUP BY bookdate
HAVING rooms > 1

Hi,

Thanks for taking the time to respond.

I tried your suggestion and created a couple of double bookings on purpose and whilst it found the double bookings I added it’s also listing lots of results that aren’t double bookings (i.e. only one instance of that room booked on a certain day). For instance booking order id (bookorderId) 8423 comes up in the results suggesting room 14 has been double booked but the database data shows that on that date there is only one entry for each room (see screenshot below):

I’m not sure whats going wrong - it seems as if it’s not grouping by the date properly.

SELECT t.bookId , t.bookdate , t.bookrm , t.bookorderId FROM ( SELECT bookdate , bookrm FROM tblbookings GROUP BY bookdate , bookrm HAVING COUNT(*) > 1 ) AS dupes INNER JOIN tblbookings AS t ON t.bookdate = dupes.bookdate AND t.bookrm = dupes.bookrm

1 Like

Hi,

Thank you so much - works a treat!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.