CODEWITHSUNDEEP
sqlsql-server
Buca
Buca

Reputation: 15

Check if a sequence of records contain a specific value

I am trying to find records not having a specific value in a sequence:

Customers Table:

enter image description here

For instance, I need to find all customers not having "Value F" on record. 

SELECT * FROM Customer
WHERE VALUE NOT IN 'Value F'

would also return values A, B, C, etc.

Upvotes: 1

Views: 70

Answers (2)

JamieD77
JamieD77

Reputation: 13949

SELECT  *
FROM    Customer c
WHERE   NOT EXISTS ( SELECT 1
                     FROM   Customer c2
                     WHERE  c2.CustomerID = c.CustomerID
                            AND VALUE = 'Value F' )

NOT EXISTS can be used here.

Your NOT IN statement would need to look like this.

SELECT  *
FROM    Customer
WHERE   CustomerId NOT IN (SELECT   CustomerID
                           FROM     Customer
                           WHERE    VALUE = 'Value F')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Use group by and having:

select customerid
from customers
group by customerid
having sum(case when value = 'Value F' then 1 else 0 end) = 0;

Upvotes: 1

Related Questions