mysql - select all rows that have more than two matching composite keys in a table -

mysql fiddle

i have composite table , i'm trying select rows has both 1 , 2 in pk2 column. can see, i've write query job using join... yeah. it's okay not satisfying.

i'm afraid if code messy , slower adding more joins in query when needs more 10 constraints.

is there faster & cleaner way same result?

assumption:

there can more 10 constraints in worst scenario. unlikely possibly.

what i've tried far:

1. i tried using union didn't work & thought it's 1 of worst options choose when comes performance efficiency.
2. i tried in (....) it's same or b... not intended.
3. i tried subquerying in clause... more constraints has, worse nightmare becomes.

this sounds "set-within-sets" query. approach group by , having. provides flexible logic handling many different situations:

select t1.pk1 t1 pk2 in (1, 2) group t1.pk1 having count(*) = 2; 

this assumes rows in table unique (otherwise use count(distinct).

here sql fiddle.