Select all columns for users in multiple groups within a case [duplicate]

Question

I'm trying to return all columns within a UserGroup table for users in multiple groups per case.

TABLE structure:

Case  Group LastName    FirstName
A       1   James       Mason
B       2   John        Abel
B       3   John        Abel
D       4   Gordon      Cathy
E       5   Baker       Phil
F       6   Green       Goldie

Desired results:

Case    Group   LastName   FirstName
B        2         John       Abel
B        3         John       Abel

I am able to run this query to return the list of duplicate users within a case belonging to multiple groups but it does not list the groups they belong to.

SELECT case, lastname, firstname, count(*) FROM table 
GROUP BY case, lastname, firstname
HAVING count(*) > 1

Thanks!

Gordon Linoff · Accepted Answer · 2019-10-23 17:28:45Z

0

I would recommend simply using exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.case = t.case and
                    t2.firstname = t.firstname and
                    t2.lastname = t.lastname and
                    t2.group <> t.group
             );

answered Oct 23, 2019 at 17:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select all columns for users in multiple groups within a case [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related