Hi I have a list of properties and a list of bookings. When I do a search I need to find the availability for a property.   I think I need a sub query. I have been looking into the ANY syntax.

Here what a sample data may look like.

Table of properties

Table of Bookings

Query to see if a property is available per booking record.

SELECT
    *
FROM
    aa_bookings 
WHERE
    aa_bookings.start > '2022-12-31'
    OR
    aa_bookings.end < '2022-12-01'

A fuller example (and verified answer to the question):

SELECT
    aa_listings.id
FROM
    (
        aa_listings
        LEFT JOIN aa_features ON aa_listings.id = aa_features.listing_id
    )
WHERE
    aa_listings.id != ''
    AND
    aa_listings.deleted != 1
    AND
    hidden != 1
    AND
    EXISTS
    (
        SELECT
            1
        FROM
            aa_bookings
        WHERE
            aa_bookings.start > '2022-07-31'
            AND
            aa_bookings.end < '2022-12-01'
            AND
            aa_bookings.listing_id = aa_listings.id
    )
    AND
    aa_features.Central Heating = 1;

I think I need to run this sub query for the property_id, so kind of needs a JOIN or something too?

Advice please :-)

CodePudding user response：

SELECT
  id
FROM properties p
WHERE NOT EXISTS (SELECT
    1
  FROM bookings b
  WHERE b.start > '2022-12-31'
  AND b.[end] < '2022-12-01'
  AND b.property_id = p.id)

you could do something like this