CODEWITHSUNDEEP
sqlsql-server
Hamed Hadian
Hamed Hadian

Reputation: 25

Query two tables - return Items that are not in both

I have two tables like this :

TableA 

Id | ProjectId | JobId

TableB 

Id | Title

I want to write a query returning TableB Ids that are not in TableA with ProjectId = 2.

I've written a query like 

Select B.Id 
From TableB B 
Right Join TableA A On B.Id = A.JobId 
Where B.JobId Is Null And A.ProjectId = 2

But it returns zero output.

Thanks

Upvotes: 0

Views: 80

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

I would use EXISTS here:

SELECT b.Id
FROM TableB b
WHERE NOT EXISTS (SELECT 1 FROM TableA a WHERE a.JobId = b.Id AND a.ProjectId = 2);

Reading in English terms, the above says to select every Id in TableB such that we cannot find an equal Id in TableA whose ProjectId is also 2.

Query edited

Upvotes: 5

Related Questions