Back To Programming / Scripting / Coding Forum

[MySQL 5] Fetching records for common dates

  • cancer10
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jun 29, 2006
  • Posts: 268
  • Status: Offline

Post December 15th, 2010, 10:52 am

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 :)
Interview Questions & Answers - http://www.focusinterview.com
My Profile: http://outlineme.com/cancer10
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post December 15th, 2010, 10:52 am

  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13458
  • Loc: Florida
  • Status: Offline

Post December 15th, 2010, 8:37 pm

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;
Strong with this one, the sudo is.
  • cancer10
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jun 29, 2006
  • Posts: 268
  • Status: Offline

Post December 15th, 2010, 8:54 pm

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
Interview Questions & Answers - http://www.focusinterview.com
My Profile: http://outlineme.com/cancer10
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13458
  • Loc: Florida
  • Status: Offline

Post December 15th, 2010, 11:38 pm

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.
Strong with this one, the sudo is.
  • cancer10
  • Proficient
  • Proficient
  • No Avatar
  • Joined: Jun 29, 2006
  • Posts: 268
  • Status: Offline

Post December 15th, 2010, 11:43 pm

Thanks man
Interview Questions & Answers - http://www.focusinterview.com
My Profile: http://outlineme.com/cancer10
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post December 16th, 2010, 8:01 am

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;
#define NULL (::rand() % 2)
  • joebert
  • Sledgehammer
  • Genius
  • No Avatar
  • Joined: Feb 10, 2004
  • Posts: 13458
  • Loc: Florida
  • Status: Offline

Post December 22nd, 2010, 9:53 pm

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

Page 1 of 1

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 147 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
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.