MySQL using count in query to search for availability of multiple rows

Question

I am using one table, mrp to store multi room properties and a second table booking to store the dates the property was booked on. I thus have the following tables:

mrp(property_id, property_name, num_rooms)

booking(property_id, booking_id, date)

Whenever a property is booked, an entry is made in the bookings table and because each table has multiple rooms, it can have multiple bookings on the same day.

I am using the following query:

SELECT * FROM mrp 
   WHERE property_id 
     NOT IN (SELECT property_id FROM booking WHERE `date` >= {$checkin_date} AND `date` <= {$checkout_date}

)

But although this query would work fine for a property with a single room (that is, it only lists properties which have not been booked altogether between the dates you provide), it does not display properties that have been booked but still have vacant rooms. How can we use count and the num_rooms table to show in my results the rooms which are still vacant, even if they already have a booking between the selected dates, and to display in my results the number of rooms that are free.

+1 for trying, giving us a good example and putting effort in writing the question. — Menno
– Menno, Commented Feb 16, 2014 at 8:45

Whitzz · Accepted Answer · 2014-02-17 08:42:28Z

1

You need 3 levels of query. The innermost query will list properties and dates where all rooms are fully booked (or overbooked) on any day within your date range. The middle query narrows that down to just a list of property_id's. The outermost query lists all properties that are NOT in that list.

SELECT *
FROM mrp
WHERE property_id NOT IN (
    -- List all properties sold-out on any day in range
    SELECT DISTINCT Z.property_id
    FROM (
        -- List sold-out properties by date
        SELECT MM.property_id, MM.num_rooms, BB.adate
        , COUNT(*) as rooms_booked
        FROM mrp MM
        INNER JOIN booking BB on MM.property_id = BB.property_id
        WHERE BB.adate >= @checkin AND BB.adate <= @checkout
        GROUP BY MM.property_id, MM.num_rooms, BB.adate
        HAVING MM.num_rooms - COUNT(*) <= 0
    ) as Z
)

answered Feb 17, 2014 at 8:42

Whitzz

5274 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1227793 Over a year ago

Great! I think we are getting close. This query will only list the properties which are available. How do I get this query to show the number of rooms available as well?

Szymon · Accepted Answer · 2014-02-16 08:46:19Z

0

You are close but you need to change the dates condition and add a condition to match the records from the outer and inner queries (all in the inner query's WHERE clause):

SELECT * FROM srp 
WHERE NOT EXISTS 
    (SELECT * FROM bookings_srp 
     WHERE srp.booking_id = bookings_srp.booking_id
     AND `date` >= {$check-in_date} AND `date` <= {$check-out_date})

answered Feb 16, 2014 at 8:46

Szymon

43k16 gold badges99 silver badges115 bronze badges

Comments

RaviH · Accepted Answer · 2014-02-16 08:52:07Z

0

You have to exclude the properties which are booked between the checkin date and checkout date. This query should do:

SELECT * FROM srp WHERE property_id NOT IN (
    SELECT property_id FROM booking WHERE `date` >= {$checkin_date} AND `date` <= {$checkout_date}
)

answered Feb 16, 2014 at 8:52

RaviH

3,5942 gold badges17 silver badges14 bronze badges

1 Comment

RaviH Over a year ago

I will be very grateful if the down voter can shed some light on why it has been down voted.

Collectives™ on Stack Overflow

MySQL using count in query to search for availability of multiple rows

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related