Many to many relationship query

Transact-SQL
1

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

2

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.

3

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'

4

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]

5

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')

image
image
image
image

1 Like