CODEWITHSUNDEEP
mysqlsql
asanas
asanas

Reputation: 4280

SQL Query - Find Duplicates with a Different Key

I have the following data:

id  userid  name    group
1   1       A       x
2   1       A       y
3   1       A       z
4   2       B       x
5   2       B       y
6   3       C       y
7   4       D       x
8   5       E       x
9   5       E       z
10  6       F       x

I want to find those records that meet all this condition:

Select all rows where the a userid belongs to a group other than y but the userid also belongs to group y.

The resulting dataset will be as follows:

id  userid  name    group
1   1       A       x
3   1       A       z
4   2       B       x

If you see, it has resulted in two records for userid a because these are two two records belong to groups other than y but the userid 1 also belongs to group y. Same for userid 2.

I have been breaking my head on how to get this in an SQL statement but not even close to a solution.

Any help is appreciated.

Upvotes: 1

Views: 57

Answers (3)

Hans
Hans

Reputation: 447

Use a join:

SELECT t1.*
FROM mytable t1
INNER JOIN mytable t2
ON t1.user_id = t2.user_id AND t1.group <> t2.group AND t2.group = 'y'

I think that would be the fastest query (but please feel free to try the other solutions as well). Add an index on user_id if not already there and maybe play with some other indexes as well (maybe a composite index on group and user_id can be utilized)

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

Use exists

select *
from MyTable a2
where name_group <> 'y'
and exists (select 1 
            from MyTable a2
            where a2.name_group = 'y' 
            and a2.userid = a1.userid)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You can get all the users that meet the condition using aggregation and having:

select userid
from t
group by userid
having sum( group = 'y' ) > 0 and
       sum( group <> 'y') > 0;

I leave it to your to put this into a query to get all the original rows.

Upvotes: 0

Related Questions