mysql - Finding and displaying duplicate values using WHERE IN to compare two values -

i have mysql database compare duplicated values based on 2 values in column. example

database

field_one | field_two | field_three  ------------------------------------ aaa       | 123       | no1 bbb       | 456       | no1 aaa       | 123       | no2 ccc       | 456       | no3  aaa       | 123       | no3 

i return these results

field_one | field_two | field_three  ------------------------------------ aaa       | 123       | no1 aaa       | 123       | no2 

here query i've been using i'm not sure how desired result. when run query in phpmyadmin browser gets stuck. database large too.

select * table_name field_three in (select field_one, field_two table_name field_three in ('no1', 'no2') having count(*) > 1)

thanks

---

solved

i changed where @gordon linoff 's query.

select t.* table_name t join      (select field_one, field_two       table_name t       group field_one, field_two       having count(*) = 2   -- or mean >= 2?      ) tsum      on t.field_one = tsum.field_one , t.field_two = tsum.field_two field3 in ('no1', 'no2') 

i think want this:

select t.* t join      (select field_one, field_two       t       group field_one, field_two       having count(*) = 2   -- or mean >= 2?      ) tsum      on t.field_one = tsum.field_one , t.field_two = tsum.field_two 

the subquery finds duplicates based on first 2 columns. outer query returns original rows.

if want duplicates relative value, add condition subquery:

select t.* t join      (select field_one, field_two       t       field3 = 'no1' -- or whatever want set       group field_one, field_two       having count(*) = 2   -- or mean >= 2?      ) tsum      on t.field_one = tsum.field_one , t.field_two = tsum.field_two