[MySQL 5] Fetching records for common dates

  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

Hi peps,

I have a query about a hotel's room booking system.

Please take a look into the room table.

Image

The following is the order table:

Image




What query would fetch me the date(s) which has all rooms (8 in this case) booked on that date?

For this case it would be 2010-12-10 and 2010-12-20, since all 8 rooms have been booked for the date.

Image




Please if anyone can help?

Many thanks in advance :)
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

If a room can't be booked more than once in the same day, something like this should work.

SQL Code: [ Select ]
SELECT date_booked, COUNT(room_id) AS rooms_booked
FROM bookings
WHERE rooms_booked = 8
GROUP BY date_booked;
  1. SELECT date_booked, COUNT(room_id) AS rooms_booked
  2. FROM bookings
  3. WHERE rooms_booked = 8
  4. GROUP BY date_booked;
  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

Hi

Thanks for your reply.

Since the number of rooms can vary, may be 10,20,30 etc, so do I need to fire a different query and find out the count and store it in a variable and then run your query?

Like this?
Code: [ Select ]
..where rooms_booked = $count
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

Sounds about right. You could probably use a subquery to get the largest room number, but there's a lot of ways that could break if rooms were removed or something.

SQL Code: [ Select ]
SELECT date_booked, COUNT(room_id) AS rooms_booked
FROM bookings
WHERE rooms_booked = (SELECT room_id FROM rooms ORDER BY room_id DESC LIMIT 1)
GROUP BY date_booked;
  1. SELECT date_booked, COUNT(room_id) AS rooms_booked
  2. FROM bookings
  3. WHERE rooms_booked = (SELECT room_id FROM rooms ORDER BY room_id DESC LIMIT 1)
  4. GROUP BY date_booked;


Chances are you're already going to have that count available in another place, so the "= $count" you have is likely going to make more sense.
  • cancer10
  • Proficient
  • Proficient
  • cancer10
  • Posts: 268

Post 3+ Months Ago

Thanks man
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

joebert wrote:
Sounds about right. You could probably use a subquery to get the largest room number, but there's a lot of ways that could break if rooms were removed or something.

SQL Code: [ Select ]
SELECT date_booked, COUNT(room_id) AS rooms_booked
FROM bookings
WHERE rooms_booked = (SELECT room_id FROM rooms ORDER BY room_id DESC LIMIT 1)
GROUP BY date_booked;
  1. SELECT date_booked, COUNT(room_id) AS rooms_booked
  2. FROM bookings
  3. WHERE rooms_booked = (SELECT room_id FROM rooms ORDER BY room_id DESC LIMIT 1)
  4. GROUP BY date_booked;


Chances are you're already going to have that count available in another place, so the "= $count" you have is likely going to make more sense.


You should be able to just count the total number of rooms.

SQL Code: [ Select ]
SELECT date_booked, COUNT(room_id) AS rooms_booked
FROM bookings
WHERE rooms_booked = (SELECT COUNT(*) FROM rooms)
GROUP BY date_booked;
  1. SELECT date_booked, COUNT(room_id) AS rooms_booked
  2. FROM bookings
  3. WHERE rooms_booked = (SELECT COUNT(*) FROM rooms)
  4. GROUP BY date_booked;
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13502
  • Loc: Florida

Post 3+ Months Ago

There was a reason I decided against doing that earlier, Spoof, but I can't remember what it was at the moment. :scratchhead:

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 140 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.