How to exclude records with certain values in sql select

exists, sql, sql-server, t-sql

Solution

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)

Problem

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

Original source