CODEWITHSUNDEEP
sql-serversql-server-2012
Jeff Yates
Jeff Yates

Reputation: 62377

How do I return a record that does not have a specific associated record in another table?

I have a table, A, that has a 1-* relationship with records in table B (i.e. there can be multiple records in B that correspond to a single record in A).

Each record in B has a value column, X.

I want to find a record from A that does not have a corresponding record in B of a specific value, Y.

For example, I have three records in A (represented here on the left as the numbers 1, 2 and 3). Each has records in B with values as represented by the comma-separated list (each separated value is a record in B).

1   a,b
2   a,c
3   b,c

If I want to get all records that DO NOT have a value of 'a' (i.e. I want record 3), how do I do this?

Upvotes: 0

Views: 59

Answers (1)

Bernhard Barker
Bernhard Barker

Reputation: 55609

You can do it by either using WHERE NOT EXISTS:

SELECT *
FROM A
WHERE NOT EXISTS (SELECT * FROM B WHERE X = 'a' AND A.ID = B.ID)

Or LEFT JOIN and WHERE ... IS NULL:

SELECT A.* 
FROM A
LEFT JOIN B ON B.ID = A.ID AND B.X = 'a'
WHERE B.ID IS NULL

B.X = 'a' is part of the JOIN condition, so only rows from B where X = 'a' will be matched up, and if no such row exists (i.e. IS NULL), we want those rows from A.

Upvotes: 2

Related Questions