CODEWITHSUNDEEP
asp.netsql
Carlo Adap
Carlo Adap

Reputation: 257

SQL retrieving data NOT IN

SELECT ID, Name
FROM dbo.AmenitiesList 
WHERE TypeID=@Type and Status = 'Available'
    AND ID NOT IN
    (SELECT AmenitiesID FROM dbo.ReservationList
     WHERE Status = 'Cancelled' AND StartDate between @Arrival and @Departure
     or EndDate between @Arrival and @Departure
     or @Arrival between StartDate and EndDate
     or @Departure between StartDate and EndDate)

This is my query, I want to display all the Available Amenities that the status is 'Cancelled' and the Arrival Date and Departure date is not between in the ArrivalDate and Departure date in Database. But when retrieving data, I didn't get the Amenities available because when the Status is cancelled it triggers the other condition about those in between dates. How to Avoid that?

I want to display Amenities that is Cancelled and also not between ArrivalDate and Departure Date

Thank you in advance guys!

Upvotes: 0

Views: 69

Answers (3)

Carlo Adap
Carlo Adap

Reputation: 257

SELECT ID, Name
FROM dbo.AmenitiesList 
WHERE TypeID=@Type and Status = 'Available'
    AND ID NOT IN
    (SELECT AmenitiesID FROM dbo.ReservationList
 WHERE Status != 'Cancelled' AND Status != 'Check Out' AND  ((@Arrival <= EndDate) AND (@Departure >= StartDate)))

This solve my problem! By the way thank you guys for spending time giving information to me! God Bless!

Upvotes: 0

muhmud
muhmud

Reputation: 4604

How about this version?

SELECT ID, Name
FROM dbo.AmenitiesList 
WHERE TypeID=@Type and Status = 'Available'
    AND ID NOT IN
    (SELECT AmenitiesID FROM dbo.ReservationList
     WHERE Status = 'Cancelled' AND (StartDate between @Arrival and @Departure
     or EndDate between @Arrival and @Departure
     or @Arrival between StartDate and EndDate
     or @Departure between StartDate and EndDate))

Upvotes: 1

LukeHennerley
LukeHennerley

Reputation: 6434

For performance and optimization, consider table variables.

DECLARE @TABLE AS TABLE
(
  AmenityID INT PRIMARY KEY CLUSTERED
)
INSERT INTO @TABLE
SELECT AmenitiesID FROM dbo.ReservationList
--Your conditions

--Get values that exist
SELECT * FROM AmentitiesList al
INNER JOIN @Table t on al.AmenityID = t.AmenityID

--Get values that don't
SELECT * FROM AmentitiesList al
LEFT JOIN @Table t on al.AmenityID = t.AmenityID
WHERE t.AmenityID IS NULL

Just because code is shorter, doesn't mean it scales. Left Joins are also a pain...

You could easily get rid of that too by using a table variable.

DECLARE @AmenityList AS TABLE
(
  AmenityID INT PRIMARY KEY CLUSTERED
  Matched BIT DEFAULT(0)
)
INSERT INTO @AmenityList
Select AmenityID FROM AmentitiesList

UPDATE @AmenityList SET Matched = 1
FROM @AmenitityList al
INNER JOIN @Table t on t.AmentityID = al.AmentityID

SELECT * FROM @AmentityList WHERE Matched = 0

Upvotes: 1

Related Questions