How to exclude records with certain values in sql select

Question

How do I only select the stores that don't have client 5?

StoreId   ClientId   
-------   ---------
  1         4     
  1         5      
  2         5     
  2         6      
  2         7   
  3         8

I'm trying something like this:

SELECT SC.StoreId FROM StoreClients
INNER JOIN StoreClients SC
    ON StoreClients.StoreId = SC.StoreId
    WHERE SC.ClientId = 5
GROUP BY StoreClients.StoreId

That seems to get me all the stores that have that client but I can't do the opposite because if I do <> 5 ill still get Store 1 and 2 which I don't want.

I'm basically trying to use this result in another query's EXISTS IN clause

Answer 1

One way:

SELECT DISTINCT sc.StoreId
FROM StoreClients sc
WHERE NOT EXISTS(
    SELECT * FROM StoreClients sc2 
    WHERE sc2.StoreId = sc.StoreId AND sc2.ClientId = 5)

Answer 2

SELECT SC.StoreId 
FROM StoreClients SC
WHERE SC.StoreId NOT IN (SELECT StoreId FROM StoreClients WHERE ClientId = 5)

In this way neither JOIN nor GROUP BY is necessary.

Answer 3

SELECT  DISTINCT a.StoreID
FROM    tableName a
        LEFT JOIN tableName b 
          ON a.StoreID = b.StoreID AND b.ClientID = 5
WHERE   b.StoreID IS NULL

• SQLFiddle Demo

OUTPUT

╔═════════╗
║ STOREID ║
╠═════════╣
║       3 ║
╚═════════╝

Answer 4

SELECT StoreId
FROM StoreClients
WHERE StoreId NOT IN (
  SELECT StoreId
  FROM StoreClients
  Where ClientId=5
)

SQL Fiddle

Answer 5

You can use EXCEPT syntax, for example:

SELECT var FROM table1
EXCEPT
SELECT var FROM table2

Answer 6

<> will surely give you all values not equal to 5. If you have more than one record in table it will give you all except 5. If on the other hand you have only one, you will get surely one. Give the table schema so that one can help you properly