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)