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  lt  gt  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

User · Answer

lt  gt  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

User · Answer

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

User · Answer

SELECT StoreId FROM StoreClients WHERE StoreId NOT IN     SELECT StoreId   FROM StoreClients   Where ClientId 5     SQL Fiddle

User · Answer

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

User · Answer

You can use EXCEPT syntax  for example   SELECT var FROM table1 EXCEPT SELECT var FROM table2

User · 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

[sql] How to exclude records with certain values in sql select

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to exists