SQLTeam.com | Weblogs | Forums

Question on complicated join

Hi,

This is a continuation of the thread here:

many-to-many-relationship-query/18235

So I have the query below:

USE AATBookings

select
*
from
CourtesyCarModels
where
CourtesyCarBookings.BookingId_Fk not in
( select
CourtesyCarBookingId
from
CalendarCourtesyCarBooking a
join
Calendar b
on a.CalendarDateId = b.ID
where
b.CalendarDate between '01-03-2021' and '02-03-2021')
AND courtesyCarBookings.CourtesyCarId_Fk = CourtesyCarModels.CourtesyCarId

The intention is to return cars free for a date. So the calendar table would store dates (basically every day of the year). If a car is reserved on a date (let's say 8-9 March), but another car is not, and a customer wants to book a car for 8-9 March, the booked car will not return in the query, but a car which is free.

Can you provide DDL and sample data?