CODEWITHSUNDEEP
sqlselectgroup-by
Princess
Princess

Reputation: 443

SQL Group By with possible subquery?

I have a User table that has a many records to 1 user relationship. I want to find all the records where the UserId's are the same and the UserName's are different.

for example I have tried: 

SELECT UserId, UserName, count(*) FROM tblUser
GROUP BY UserId, UserName

This just gives me a list of users and how many times they have that distinct combination of UserId and UserName

so my desired output would be 

UserId    | UserName
----------+-----------
1111      | Bob
1111      | JoeBob
2222      | Jimmy
2222      | ILikeTurtles

Upvotes: 0

Views: 130

Answers (3)

Set
Set

Reputation: 391

select t.userid, t.username
from tbluser t
where exists(select 1 
             from tbluser t2
             where t.userid = t2.userid
               and t.username <> t2.username)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I am going to interpret what you say as: "and the user names are different from the dominant name".

So, the problem is to find the most common name, and then find all other rows besides that one.

with tgrp as (select tu.userid, tu.username, count(*) as cnt
              from tblUser tu
              group by tu.userid, tu.username
             ),
     tmax as (select tu.userid, tu.username, cnt
              from tgrp join
                   (select tu.userid, max(cnt) as maxcnt
                    from tgrp
                    group by tu.userid
                   ) t1
                   on tgrp.userid = t1.userid and tgrp.cnt = t1.maxcnt
             )
select *
from tblUser tu left outer join
     tmax
     on tu.userid = tmax.userid and tu.username = tmax.username
where tmax.userid is null

This query does it step-by-step. Find the counts for all combinations of id and name. Then find the max combination. Then find everything that is not the max combination.

If you just want the userids that have multiple names, you can use:

select userid
from tblUser tu
group by userid
having count(distinct username) > 1

To get the full record, join this back to the original table:

select *
from tbl_user tu
where tu.userid in (select userid
                    from tblUser tu
                    group by userid
                    having count(distinct username) > 1
                   )

Upvotes: 1

Derek
Derek

Reputation: 23238

select *
from tblUser t1
inner join (
  SELECT UserId
  FROM tblUser 
  GROUP BY UserId
  having count(distinct UserName) > 1
) t2
  on t1.UserID = t2.UserID

Upvotes: 3

Related Questions