MYSQL query to get rows from same table where specific value is different

Question

I am not very good in SQL-Queries and got stuck with a query.

I have the following (simplified) table structure:

hash  state  group
x123    1     41
x456    2     44
x123    3     41
x456    2     44
x123    2     42
...    ...   ...

The hash is an identifier for a person which can be part of one or more groups.

I want to get all rows of persons wich are in more than one group. So from my example I would get all three rows of the person with the hash "x123", but none from the person with the hash "x456".

It would be nice, if anyone could help me, thanks a lot!

@Barmar that duplicate matches the question perfectly, but didn't have a window function answer, so I added one — ysth, Aug 11 '20 at 16:47
Yeah, lots of canonical dups need to be updated with window function solutions. — Barmar, Aug 11 '20 at 16:48
Ah I did not find that question, thanks. The most upvoted example does not work for me. It gives me all rows, dont know why. M Khalid Junaids answer did the trick. — frankvanlampe, Aug 12 '20 at 13:48

score 1 · Accepted Answer · answered Aug 11 '20 at 16:38

1

You can use WHERE EXISTS with some aggregate condition

select *
from users u
where exists (
  select 1
  from users
  where hash= u.hash
  having count(distinct `group`) > 1
)

DEMO

answered Aug 11 '20 at 16:38

M Khalid Junaid

63,861
10
90
118

@frankvanlampe [How to say thanks on SO](https://stackoverflow.blog/2020/06/17/saying-thanks-testing-a-new-reactions-feature/#:~:text=On%20the%20left%20of%20the,thanks%20to%20the%20post%2Dauthor.) please have a look or mark the asnwer as accepted – M Khalid Junaid Aug 12 '20 at 13:50

MYSQL query to get rows from same table where specific value is different

1 Answers1