Andrew Andrew - 4 months ago 10
SQL Question

Selecting items that have same values in the same column

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

product_filter
.

The structure:

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


I need to submit a list of products (for example 1,2,3) and get only those filter combinations, that are the same for all selected product id's. So the result needs to be

filter1_id | filter2_id
2 | 1


My problem is that my products might vary and I can't do a ton of self inner joins... so I am stuck... Any advise?

Answer

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.