0

So I have a set of user_id who buy at store_id.

Id like to retrieve a list of users who have made purchases at store_id 1 exclusively.

Meanining, if they bought at store_id 1 and 2, they need to be filtered out.

I could say WHERE store_id IN (1) and store_id NOT IN (2,3,4)?

Also this might not be an options because there are too many store IDs to get a list.

Eduardo Chacon
  • 9
  • 1
  • Please provide sample data, table structure and expected output. Please tag your rdbms as well. thank you – learning Dec 16 '22 at 05:57

1 Answers1

0

We can use NOT EXISTS in a subquery:

SELECT y.user_id
FROM yourtable y
WHERE y.store_id = 1
AND NOT EXISTS
  (SELECT 1 FROM yourtable y1 
     WHERE y.user_id = y1.user_id
     AND y1.store_id <> 1);

The main query will select only those users which appear in store 1 and the sub query will make sure they do dot appear in other stores, too.

Thus, only those users will be selected which occured in store 1 only.

See here with some sample data: db<>fiddle

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
  • What does Select 1 refer to? – Eduardo Chacon Dec 16 '22 at 06:14
  • It doesn't really matter what we select there. EXISTS checks whether at least one entry exists. Often people just select 1, I guess because 1 is known as true and 0 as false. But if you dislike this, you can also select a column instead. See also here: https://stackoverflow.com/questions/26461868/exists-not-exists-select-1-vs-select-field – Jonas Metzler Dec 16 '22 at 06:20