Select all columns for users in multiple groups within a case

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!

Answer 1

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
             );