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.