How to exclude records with certain values in sql

问题:

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

回答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)

回答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.

回答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 ║
╚═════════╝

回答4:

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

SQL Fiddle

回答5:

You can use EXCEPT syntax, for example:

SELECT var FROM table1
EXCEPT
SELECT var FROM table2

回答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