Exclude entire group when one condition matches-CodePudding

Let's say I have a table like such:

Store         productId
Test 1          6524
Test 1          6521
Test 1          6523
Test 2          6234
Test 2          6264
Test 3          6395

I am trying to find a way to check whether the store has the product 6524 and if it does it should remove the entire group (store) from the list so that the result can look like the below.

Expected Outcome:

Test 2
Test 3

I have tried doing a GROUP by followed by HAVING productId <> 6524 but this of course just gets rid of the single row not the entire group.

CodePudding user response：

You can use NOT EXISTS such as

SELECT DISTINCT Store
  FROM tab t
 WHERE NOT EXISTS ( SELECT 0 
                      FROM tab 
                     WHERE Store = t.Store
                       AND productId = 6524  )

which filters out the matching values of Store column

CodePudding user response：

select store
  from your_table
 group by store
having sum(productId=6524)=0;