MySQL uses a counter in a query to search for multiple row availability

Question

I use one table, mrp, to store the properties of several rooms and a second reservation table to save the dates on which the property was booked. So I 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 recorded in the reservation table and because there are several rooms in each table, it can have multiple orders 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}

)

( , , ), , , . num_rooms, , , , .

Answer 1

3 . , ( ) . . , .

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
)

Answer 2

, ( WHERE):

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})

Answer 3

You must exclude properties that are booked between the check-in date and check-out date. This request should execute:

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