I am facing a conundrum; not sure why -- is it because this late, or I am just stuck. My goal is to create a filter on the webpage, so I am trying to figure this out.
I have a list of products that I store with filters in the reference table
id | product_id | filter1_id | filter2_id
1 | 1 | 2 | 1 <---
2 | 1 | 4 | 3
3 | 1 | 5 | 1
4 | 2 | 2 | 1 <---
5 | 2 | 3 | 1
6 | 3 | 2 | 1 <---
7 | 3 | 3 | 4
filter1_id | filter2_id
2 | 1
Here is one approach that you could try:
select filter1_id, filter2_id from product_filter group by filter1_id, filter2_id having count(*)=( select count(distinct product_id) from product_filter )
This will only return a list when a combination of filter1_id and filter2_id exists for every product_id. (Fiddle here.) Is that what you are after? Do you don't mention what should be returned if there isn't any combination that exists for all of the given product_id's - an empty result set?
It's not a self-join (or even a ton of them ;) ) but it will still be fairly expensive I imagine.