sql server - 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)