You're basically asking to find the intersection of N comma-separated lists, from the set of lists found by looking up N item_id's.
The way you're storing comma-separated lists really does make this harder.
To solve this puzzle, I can think of a way:
Create a table with every store_id.
mysql> create table stores (store_id int primary key);
mysql> insert into stores values (101), (102), (103), (104);
Now you can transform the set of stores in your comma-separated list into a number of rows, based on matching the CSV to the individual values in the stores table.
mysql> select * from mytable as t join stores as s on find_in_set(s.store_id, t.store_id)
where item_id in (2,3,4);
+---------+-----------------+----------+
| item_id | store_id | store_id |
+---------+-----------------+----------+
| 3 | 101,102,103,104 | 101 |
| 2 | 102,104 | 102 |
| 3 | 101,102,103,104 | 102 |
| 4 | 102,103,104 | 102 |
| 3 | 101,102,103,104 | 103 |
| 4 | 102,103,104 | 103 |
| 2 | 102,104 | 104 |
| 3 | 101,102,103,104 | 104 |
| 4 | 102,103,104 | 104 |
+---------+-----------------+----------+
Then group these rows by store_id. Those groups that have a count of N, given that you're searching for N item_id's, are the ones that meet the criteria.
mysql> select s.store_id, count(*)
from mytable as t join stores as s on find_in_set(s.store_id, t.store_id)
where item_id in (2,3,4)
group by s.store_id;
+----------+----------+
| store_id | count(*) |
+----------+----------+
| 101 | 1 |
| 102 | 3 |
| 103 | 2 |
| 104 | 3 |
+----------+----------+
You can filter the groups down to the ones you want by using a HAVING clause.
mysql> select s.store_id, count(*)
from mytable as t join stores as s on find_in_set(s.store_id, t.store_id)
where item_id in (2,3,4)
group by s.store_id
having count(*) = 3;
+----------+----------+
| store_id | count(*) |
+----------+----------+
| 102 | 3 |
| 104 | 3 |
+----------+----------+
The problem with this is not only does it require you to have another table that lists all your store_ids, but the FIND_IN_SET() function is inefficient and can't be optimized with an index. It's bound to do a cross-join, which means for X items and Y stores, it'll do a table-scan of X items, and for each item, it'll do a table-scan of Y stores. As you get more stores and items, the performance will get worse in proportion to X * Y.
This is why it's often a bad idea to store comma-separated lists of id's, when you really need to do queries that compare to individual id's within those strings.
What you should have is three tables:
- one table for items
- one table for stores
- one table that enumerates every pair of item & store
Example:
mysql> create table store_has_item (item_id int, store_id int, primary key (item_id, store_id));
Fill this with one row per pair of item & store:
mysql> insert into store_has_item
-> select t.item_id, s.store_id from mytable as t join stores as s on find_in_set(s.store_id, t.store_id);
Query OK, 15 rows affected (0.02 sec)
Records: 15 Duplicates: 0 Warnings: 0
Now you can query just this single table for N items, group by store_id, and return those groups with a count of N:
mysql> select store_id from store_has_item where item_id in (2,3,4)
group by store_id having count(*) = 3;
+----------+
| store_id |
+----------+
| 102 |
| 104 |
+----------+
