CODEWITHSUNDEEP
mysqlsqlexists
lepp
lepp

Reputation: 13

Exclude all values except one if that one value exists in SQL

I'm trying to exclude specific values based upon whether or not one value exists. So I have data similar to that below.

Client    StoreType   
-------   ---------
   1         1     
   1         2      
   1         2     
   2         2      
   3         1   
   3         2
   4         2
   4         2

So in this example when StoreType=1 exists, I would want to exclude when StoreType=2 for that Client. However, if StoreType=1 does not exist for that Client, I would want to include when StoreType=2. So the results I would like to get would be:

Client    StoreType
-------   ---------
   1         1
   2         2
   3         1
   4         2
   4         2

Does anyone know how you could do this?

Upvotes: 0

Views: 887

Answers (2)

Marion
Marion

Reputation: 71

IF EXISTS(SELECT Client, StoreType
FROM tableFoo
WHERE StoreType = 1)

SELECT Client, StoreType
FROM tableFoo
WHERE StoreType = 1

ELSE 
IF NOT EXISTS (SELECT Client, StoreType
FROM tableFoo
WHERE StoreType = 1)

SELECT Client, StoreType
FROM tableFoo
WHERE StoreType = 2

Upvotes: 0

Barmar
Barmar

Reputation: 780818

SELECT Client, StoreType
FROM YourTable AS t1
WHERE StoreType = 1
OR (NOT EXISTS (SELECT 1 FROM YourTable AS t2
                WHERE t1.Client = t2.Client
                AND t2.StoreType = 1)
    AND StoreType = 2)

DEMO

Upvotes: 1

Related Questions