how can exclude id with having multiple value?-CodePudding

i have 3 store_id (0,1,6) . i only want to get id have storeid <> 0 , but 24340 also have storeid =0 , i also want remove it too

Original:

valueid   store_id
24346     6
24345     6
24345     1
24344     0
24340     6
24340     1
24340     0

Expected:

valueid   store_id
24346     6
24345     6
24345     1

tried using store_id NOT IN (0,1,6) or store_id<>0 And store_id<>1 AND store_id<>6 but seeem not working

CodePudding user response：

Try:

select * 
from my_tbl 
where valueid not in (select valueid 
                     from  my_tbl 
                     where store_id=0);

Demo: https://www.db-fiddle.com/f/4raGrSSZtagsBMDereNzTa/0

Result:

valueid   store_id
24346      6
24345      6
24345      1

CodePudding user response：

Using aggregation:

SELECT valueid, store_id
FROM yourTable
WHERE valueid IN (SELECT valueid FROM yourTable
                  GROUP BY valueid HAVING SUM(store_id = 0) = 0);