CODEWITHSUNDEEP
sqlsql-server
Simon Rosengren
Simon Rosengren

Reputation: 269

Problems with SQL 'WHERE NOT EXISTS'

I am building a booking system for cabins, and I am trying to type out all of the Cabins which are not booked over a specific period of weeks.

The Booking table: 

CREATE TABLE Booking
(Customer_IdNr char(10), 
CONSTRAINT test
FOREIGN KEY (Customer_IdNr)
REFERENCES Customer(IdNr)
ON DELETE CASCADE,

CabinNr integer, 
BikeNr integer,
SkiNr integer,
TotalPrice integer,
StartWeek integer,
endWeek integer,)

and here I am trying to locate the cabins which are not booked: 

SELECT Booking.CabinNr FROM Booking
WHERE NOT EXISTS 
(SELECT * FROM Booking
 WHERE 
    StartWeek >= '11'
    AND endWeek <= '15'
)

I have 5 cabins in my table, which all have at least one booking.

I only seem to be able to either locate all the cabins from weeks where nothing is booked, and if I search a week where for instance two cabins are booked, the result is no cabins at all.

Upvotes: 0

Views: 248

Answers (3)

Matt
Matt

Reputation: 15061

Join your sub query to your main query

SELECT a.CabinNr FROM Booking a
WHERE NOT EXISTS 
(SELECT * 
 FROM Booking b
 WHERE b.StartWeek >= '11'
 AND b.endWeek <= '15'
 AND b.CabinNr = a.CabinNr)

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

Your subquery is not correlated to the main query. Also, you wrapped the values for StartWeek and endWeek in single quotes but the datatypes are int. Something like this is probably what you are looking for.

SELECT Booking.CabinNr 
FROM Booking b
WHERE NOT EXISTS 
(
    SELECT * 
    FROM Booking b2
    WHERE StartWeek >= 11
        AND endWeek <= 15
        AND b2.CabinNr = b.CabinNr
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need a correlated subquery. Your version will either return all cabins or none -- depending on whether the subquery returns zero rows or more than one.

Here is the format:

SELECT b.CabinNr
FROM Booking b
WHERE NOT EXISTS (SELECT 1
                  FROM Booking b2
                  WHERE b2.StartWeek >= 11 b2.endWeek <= 15 AND
                        b2.CabinNr = b.CabinNr
                 );

Note: don't use single quotes for numeric constants. Under some circumstances, this can confuse the optimizer (as well as other people reading the query).

Upvotes: 3

Related Questions