CODEWITHSUNDEEP
mysqlsql
Anton Timmerman
Anton Timmerman

Reputation: 11

Filter Results in SQL

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.

Upvotes: 0

Views: 77

Answers (4)

ysth
ysth

Reputation: 98398

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;

Upvotes: 0

Chris
Chris

Reputation: 168

You can use this query : 

SELECT * from table where id in 
( SELECT id FROM table group by id having count(id) > 1 )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

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.

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

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

Upvotes: 3

Related Questions