TSQL Query Help Join Function

I created a simple query for attempting to find out what villa would be free in a certain range of da开发者_开发问答ys (checkin_date and checkout_date).

However now that I think about it I don't think my below query would return the correct results:

SELECT DISTINCT default_villa_type  
FROM villa, reservation  
WHERE villa.villa_type = reservation.default_villa_type  
and res_checkin_date not between '2011-12-21' and '2011-12-23'
and res_checkout_date not between '2011-12-21' and '2011-12-23'

I need the query to use:

The villa table with the following columns:

(villa_id, phone_ext, villa_type, no_of_rooms, no_of_beds, default_price_plan)

The reservation table with the following columns:

(confirm_no, credit_card_no, res_checkin_date, res_checkout_date, default_villa_type, price_plan)

I don't believe it is properly using both tables to determine which type of villas are still available under the dates inputed into the query, but I could be wrong I need a fresh pair of eyes to take a look at it.

You can try to find all the villas that are NOT free first since you don't have records that represent free villas, rather, only records representing villas that are not free.

SELECT  villa_id
FROM    villa
WHERE   villa_type NOT IN (
    SELECT  default_villa_type
    FROM    reservation
            --reservations starting within range
    WHERE   res_checkin_date > '2011-12-21'
            AND res_checkin_date < '2011-12-23'

            --reservations starting before range and ending after range
            OR (
                res_checkin_date <= '2011-12-21'
                AND res_checkout_date > '2011-12-21'
            )

            --reservations ending within range
            OR (
                res_checkout_date > '2011-12-21'
                AND res_checkout_date < '2011-12-23'
            )
)

Another way, per Mikael's comment, can be:

SELECT  villa_id
FROM    villa
WHERE   villa_type NOT IN (
    SELECT default_villa_type
    FROM   reservation
    WHERE  res_checkin_date < '2011-12-23'
           AND res_checkout_date > '2011-12-21'
)

I would also suggest you use variables instead of static string-dates.