MariaDB - search over multiple rows with same id

Question

I have table with three columns in it. First is product_id linked to another table, second is att_id with link to another table and last one is val_id linked to different table.

There are rows with same product_id with val_id and att_id combinations. Every product_id can have different number of val_id (as seen in below example). Table looks like:

product_id	att_id	val_id
77	1	7
78	1	10
78	1	2
79	3	2
79	2	1
79	2	1
80	3	2
80	2	1
80	1	1
81	2	2
81	1	1
81	2	1
82	2	1
82	2	1
83	2	1
84	3	2
85	3	2
85	2	2
85	2	1

My goal is to select product_id where val_id is equal to (1 AND 2). There can be more val_ids based on user input.

So, expected result from example above should be:

product_id
79
80
81
85

I cannot figure it out since every product_id, val_id combination is in different row.

So any help will be appreciated.

Answer 1

I think you are looking for something like:

select product_id
from test_tbl
where val_id in (1,2) 
group by product_id
having count(distinct val_id) = 2;

Result:

product_id
79
80
81
85

Demo

Answer 2

select product_id
  from test_tbl
  where val_id = 1
intersect
select product_id
  from test_tbl
  where val_id = 2

see my earlier comments.