Buca Buca - 4 months ago 8
SQL Question

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.

Answer
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') 
Comments