0

Suppose I have a table

  id            value
------        ---------
  10              123
  10              422
  11              441
  11              986
  12              674
  13              648

I need a query which will return only those id's which have 2 or more values associated with them. So, in that case it will only return ID 10 & 11, but i need al the records.

so the result looks like:

 id            value
------        ---------
  10              123
  10              422
  11              441
  11              986

Thank you.

JohnHC
  • 10,935
  • 1
  • 24
  • 40
Anton Timmerman
  • 11
  • 1
  • Write a subquery that gets the count of rows for each ID, using `COUNT(*)` and `GROUP BY`. Then join that with the table and filter it to `count >= 2`. – Barmar Jul 03 '17 at 09:31
  • Possible duplicate of [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) – Blank Jul 03 '17 at 09:36

4 Answers4

3
select a2.*
from MyTable a2
inner join
(
select a1.id
from MyTable a1
group by a1.id
having count(*) > 1
) a3
on a3.id = a2.id
JohnHC
  • 10,935
  • 1
  • 24
  • 40
0

Assuming a UNIQUE KEY can be formed on (id,value)...

SELECT DISTINCT x.*
           FROM my_table x
           JOIN my_table y
             ON y.id = x.id
            AND y.value <> x.value

If a UNIQUE KEY cannot be formed on (id,value), then this isn't really a table in a strict RDBMS sense.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
0

You can use this query : 

SELECT * from table where id in 
( SELECT id FROM table group by id having count(id) > 1 )
Yagami Light
  • 1,756
  • 4
  • 19
  • 39
Chris
  • 158
  • 8
0

With mysql 8+ or mariadb 10.2+, you would use the count window function:

select id, value
from (
    select id, value, count(id) over (partition by id) as num_values
    from sometable
) foo
where num_values > 1;
ysth
  • 96,171
  • 6
  • 121
  • 214