Hi,
I have these tables:
CALENDAR
ID
Date
CALENDARCOURTESYCARBOOKING
CourtesyCarBookingId
CalendarDateId
COURTESYCARBOOKING
CourtesyCarId
BookingId
CourtesyCarModelId
It's a M2M relationship.
What I am wondering is how can I say get the courtesy cars where there isn't an entry in the join table for a range of dates?
So in pseudo terms, GET ALL courtesy cars where DATES ARE NULL
Typically you'd use a NOT EXISTS(...) on the join table.
I can't picture your data well enough to be any more specific than that.
I wrote something like this. Let me add not exists.
SELECT *
from courtesyCarBookings
JOIN CalendarCourtesyCarBooking pt ON courtesyCarBookings.CourtesyCarBookingId = pt.CourtesyCarBookingId
JOIN Calendar t ON pt.CalendarDateId = t.id
WHERE t.CalendarDate = '20200101'
Would something like this be what you mean?
SELECT *
from courtesyCarBookings
JOIN CalendarCourtesyCarBooking pt ON courtesyCarBookings.CourtesyCarBookingId = pt.CourtesyCarBookingId
JOIN Calendar t ON pt.CalendarDateId = t.id
WHERE NOT EXISTS
(
SELECT Calendar.CalendarDate
FROM Calendar
WHERE Calendar.CalendarDate = '20200101'
)
These are the tables:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [CourtesyCarBookingId], [CalendarDateId]
,[CalendarDateId]
FROM [AATBookings].[dbo].[CalendarCourtesyCarBooking]
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [Id]
,[CalendarDate]
FROM [AATBookings].[dbo].[Calendar]
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [CourtesyCarBookingId]
,[startDate]
,[endDate]
,[CourtesyCarId_Fk]
,[BookingId_Fk]
FROM [AATBookings].[dbo].[courtesyCarBookings]
hi
++++++
how can I say get the courtesy cars where there isn't an entry in the join table for a range of dates?
++++++
i tried to do this !!
i created sample data ..and drop create tables script
wrote some SQL Query
please click arrow to the left for Drop Create Sample data
drop table CALENDAR
go
create table CALENDAR
(
ID int ,
Date date
)
go
insert into CALENDAR select 1 , '2019-10-09'
insert into CALENDAR select 2 , '2019-10-15'
insert into CALENDAR select 3 , '2019-10-20'
go
select 'Sample data Calendar', * from Calendar
drop table CalendarCourtesyCarBooking
go
create table CalendarCourtesyCarBooking
(
CourtesyCarBookingId int ,
CalendarDateId int
)
insert into CalendarCourtesyCarBooking select 1,1
insert into CalendarCourtesyCarBooking select 2,2
insert into CalendarCourtesyCarBooking select 3,3
go
select 'Sample data CalendarCourtesyCarBooking' , * from CalendarCourtesyCarBooking
go
drop table CourtesyCarBooking
go
create table CourtesyCarBooking
(
CourtesyCarId int ,
BookingId int ,
CourtesyCarModelId int
)
go
insert into CourtesyCarBooking select 1,1,1
insert into CourtesyCarBooking select 2,2,2
insert into CourtesyCarBooking select 3,2,3
go
select 'Sample Data CourtesyCarBooking' , * from CourtesyCarBooking
go
select
*
from
CourtesyCarBooking
where
BookingId not in
( select
CourtesyCarBookingId
from
CalendarCourtesyCarBooking a
join
Calendar b
on a.CalendarDateId = b.ID
where
b.Date between '2019-10-12' and '2019-10-16')
1 Like