MisterIsaak MisterIsaak - 24 days ago 13
SQL Question

How to exclude records with certain values in sql select

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

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