CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7532

SELECT all elements that don't exist in another table

I have two tables, a USER table which contains all current users. The next table is called tasks and has a lot of information, one of the columns is PointPerson which is the same as one of the columns in the user table. This column has entries for people who are not users anymore. This is what I want to get from a SELECT statement. I want all of the users in the task table that don't exist on the user table.

This is what I have tried but I am getting no responses which can't eb correct:

SELECT DISTINCT TSK.PointPerson FROM [Task] AS TSK
WHERE NOT EXISTS (
SELECT DisplayName FROM [User]
)

What am I doing wrong and how do I get the desired result?

Upvotes: 1

Views: 2376

Answers (3)

EricZ
EricZ

Reputation: 6205

Another way to do this

SELECT PointPerson 
FROM [Task]
EXCEPT
SELECT DisplayName 
FROM [User]

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

Try this:

SELECT DISTINCT TSK.PointPerson FROM [Task] AS TSK
WHERE TSK.PointPerson NOT IN (
SELECT DisplayName FROM [User]
)

Or a different approach:

SELECT DISTINCT TSK.PointPerson FROM [Task] AS TSK
LEFT JOIN [User] AS U ON U.DisplayName = TSK.PointPerson 
WHERE U.DisplayName IS NULL

Upvotes: 8

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

You may try like this:-

SELECT DISTINCT TSK.PointPerson
FROM [Task]
WHERE NOT EXISTS 
    (SELECT DisplayName  
     FROM User
     WHERE Task.name = User.name)

or using a LEFT JOIN like this:-

SELECT DISTINCT TSK.PointPerson
FROM Task t1
LEFT JOIN User t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Upvotes: 2

Related Questions