CODEWITHSUNDEEP
sqlsql-server
SimonJ
SimonJ

Reputation: 1

SQL Server - Query Result where field cant = 1

I need to list rooms where there are no bookings on day 1

I have tried 

select HotelRooms
from tblRoom
Where Day <> 1

This shows me the rooms in use on days not equal to 2. But I have a few rooms that are used on day 1, 2 and 3 and because of this these rooms still show.

I have searched and have tried to figure it out for hours, so any help would be appreciated.

Upvotes: 0

Views: 60

Answers (2)

sgeddes
sgeddes

Reputation: 62861

I like the NOT EXISTS approach, but you can also use NOT IN:

SELECT  Rooms
FROM    tblRooms
WHERE Rooms NOT IN (
   SELECT Rooms
   FROM tblRooms
   WHERE day = 1)

Or what I prefer, joining the table with itself and checking for NULL with a LEFT JOIN:

SELECT  a.Rooms
FROM    tblRooms a
  LEFT JOIN tblRooms a2 On a.Rooms = a2.Rooms And a2.Day = 1
WHERE a2.Rooms Is NULL

Upvotes: 1

John Woo
John Woo

Reputation: 263933

SELECT  a.Rooms
FROM    tblRooms a
WHERE   NOT EXISTS
        ( 
            SELECT  b.Rooms
            FROM    tblRooms b
            WHERE   a.Rooms = b.Rooms AND
                    b.Day = 1
        )

The records may be different from yours but the idea or thought of the query is still the same.

Assuming you have this records,

╔═══════╦═════╗
║ ROOMS ║ DAY ║
╠═══════╬═════╣
║     1 ║   1 ║
║     1 ║   2 ║
║     2 ║   2 ║
║     3 ║   3 ║
║     4 ║   1 ║
║     4 ║   2 ║
║     4 ║   3 ║
╚═══════╩═════╝

the OUTPUT of the query above is

╔═══════╗
║ ROOMS ║
╠═══════╣
║     2 ║
║     3 ║
╚═══════╝

Upvotes: 3

Related Questions