SQL retrieving data NOT IN

Question

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!

all that date logic could be simplified down to (@Arrival <= EndDate) AND (@Departure >= StartDate) — Marc B
– Marc B, Commented Mar 4, 2013 at 16:33

LukeHennerley · Accepted Answer · 2013-03-04 16:35:30Z

1

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

answered Mar 4, 2013 at 16:35

LukeHennerley

6,4542 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Carlo Adap Over a year ago

I don't understand this :/ sorry @Luke i'm an amateur in SQL.

LukeHennerley Over a year ago

It's okay :) I have put some comments with the code, it's more about researching what you don't know in the above code. What I have posted makes SQL optimize things so much better, giving you better perforkmance and scalability in the future.

Carlo Adap Over a year ago

okay thank you i'll research more about SQL thank you again @Luke

LukeHennerley Over a year ago

What you are doing here is getting a list of ID based on your condition (Like in the NOT IN), you can then manipulate that list to do fast things such as joins NOT IN is usually a last resort in most cases :)

muhmud · Accepted Answer · 2013-03-04 16:35:53Z

1

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

answered Mar 4, 2013 at 16:35

muhmud

4,6242 gold badges20 silver badges24 bronze badges

Comments

Carlo Adap · Accepted Answer · 2013-03-04 17:10:43Z

0

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!

answered Mar 4, 2013 at 17:10

Carlo Adap

2671 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL retrieving data NOT IN

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related