2

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.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
Venom
  • 23
  • 3
  • val_id cannot be equal to 1 and at the same time be equal to 2. – Gerard H. Pille Feb 13 '22 at 19:05
  • 1
    @BrendanMcCaffrey, on the contrary. He is looking for the intersection of the products having a val_id of 1 with the products having a val_id of 2, ie. the products having both a val_id 1 and 2. https://en.wikipedia.org/wiki/Intersection_(set_theory) – Gerard H. Pille Feb 13 '22 at 19:14
  • Yeah, in English "and" and "or" are sometimes synonyms. Very frustrating for mathematicians and programmers. – Rick James Feb 13 '22 at 22:10
  • Duplicate of https://stackoverflow.com/questions/16704290/how-to-return-rows-that-have-the-same-column-values-in-mysql?lq=1 – Barmar Feb 14 '22 at 06:03

2 Answers2

5

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

Ergest Basha
  • 3,935
  • 3
  • 6
  • 20
  • 1
    Not that it should make any logical difference but count(distinct val_id) should never be > 2. Maybe want to re-write the HAVING clause as having count(distinct val_id)=2; for better readability? – J.D. Feb 14 '22 at 04:38
3
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.

Gerard H. Pille
  • 3,255
  • 1
  • 9
  • 13