CODEWITHSUNDEEP
sqlsql-server
Chase Ernst
Chase Ernst

Reputation: 1155

Finding all items that do not exist in table - SQL

I am I having trouble getting my SQL query to return the data that I need. What I am trying to do is get all of the records that are missing out of a table. So if I have records : 92401, 92402, 92404, 92406 I want the query to return 92403, 92405.

This is the query that I am trying to use right now:

select TicketNum
from Tickets
where not exists(select TicketNum
             from TicketBooks
             where TicketBooks.TicketNum=Tickets.TicketNum)

Currently this will return no data. If I change the where clause to just exists then it will return all of the records that are in the table.

The values 92403, 92405 do not exist in Tickets, but they exist in TicketBooks.

Any suggestions on how I can modify my query to show such results?

Upvotes: 0

Views: 67

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460168

The values 92403, 92405 don't exist in Tickets, but they exist in TicketBooks.

So you want to check the opposite:

SELECT TicketNum FROM TicketBooks
WHERE NOT EXISTS
(
    SELECT 1 FROM Tickets WHERE Tickets.TicketNum = TicketBooks.TicketNum
)

Upvotes: 2

Related Questions