How to make a SQL query for multiple values ​​of one field?

Question

SQL

Example table:

| product_id | field_id | value_id |
|     1      |  f1      |     v1   |
|     2      |  f1      |     v2   |
|     3      |  f2      |     v3   |
|     4      |  f2      |     v4   |

Get product_id provided that value_id in (v1, v2) AND value_id in (v3, v4 ) there may be values ​​for one field that match (for example, for a product with field_id = f2 and value_id in (v3, v4) ). Although, if done correctly, it might work. I could not get :)

Answer 1

I don't know what it's called correctly. "Interrow AND on column values" is implemented by intersecting one table with itself as many times as there are and members to compare.
In general, such a construction is implemented in the exists expression.

select ext.*
from ext
where 
exists (select 1 from t as t1
                         join t as t2 on t1.field_id = t2.field_id
                          where t1.field_id = ext.f1
                              and t1.value_id = v1 
                              and t2.value_id = v2 )
and exists (select 1 from t as t1
                         join t as t2 on t1.field_id = t2.field_id
                          where t1.field_id = ext.f2
                              and t1.value_id = v3 
                              and t2.value_id = v4 )

Answer 2

You can try like this:

SELECT product_id, 
        SUM(CASE WHEN field_id = 'f1' AND value_id in (v1, v2) THEN 1 ELSE 0 END), 
        SUM(CASE WHEN field_id = 'f2' AND value_id in (v3, v4) THEN 1 ELSE 0 END)
   FROM product
  WHERE value_id In (v1, v2, v3, v4) 
  GROUP BY product_id
 HAVING SUM(CASE WHEN field_id = 'f1' AND value_id in (v1, v2) THEN 1 ELSE 0 END) = 1
    AND SUM(CASE WHEN field_id = 'f2' AND value_id in (v3, v4) THEN 1 ELSE 0 END) = 1

Answer 3

I didn’t quite understand what you wanted and what is impossible with having count, but in my opinion it looks like this:

SELECT product_id, count(distinct field_id) cnt_f, count(distinct value_id) cnt_v
from product
where (   (field_id =  'f1' and value_id in ('v1', 'v2')) 
          or  (field_id =  'f2' and value_id in ('v3', 'v4'))
           )
group by  product_id
having count(distinct field_id) = 2 and count(distinct value_id) = 2

If you need 2 unique values, if you are a fighter for duplicates, you can enter count(distinct value_id) -> count(*) = 2 instead