Find rows in a table where field1 is the same and field 2 is different

I have rows of a table that stores a UserID, an EnrollmentID, and other data. I want to get all records where there is more than one occurrence of a UserID and EnrollmentID.

Sample data:

serial_no  userID  EnrollmentID
-------------------------------
1234       100     44
1235       100     55
1236       200     33
1237       300     66
1238       400     88
1239       400     77

I'd want the following rows returned:

1234       100     44
1235       100     55
1238       400     88
1239       400     77

EDIT: To clarify开发者_StackOverflow社区, I want all rows where the userid exists with different enrollment id's

SQL Server 2005 solution

select * from 
(
    select *, c = COUNT(*) over (partition by userID)
    from sampletable
) sq
where c > 1

or more generically

select *
from sampletable
where userid in
(
    select userid
    from sampletable
    group by userid
    having COUNT(*) > 1
)

Using this sample

create table sampletable (serial_no int, userid int, enrollmentid int)
insert sampletable select 1234 ,100 ,44
insert sampletable select 1235 ,100 ,55
insert sampletable select 1236 ,200 ,33
insert sampletable select 1237 ,300 ,66
insert sampletable select 1238 ,400 ,88
insert sampletable select 1239 ,400 ,77

Output is

serial_no  userid  enrollmentid
1234        100    44
1235        100    55
1238        400    88
1239        400    77

select a.serial_no, a.userID, a.EnrollmentID  
from a tableA 
where a.userID in (  
SELECT DISTINCT a.userID
FROM tableA  a, tableA  b
WHERE a.userID = b.userID
AND a.EnrollmentID <> b.EnrollmentID )

SELECT a.* FROM Table1 a
INNER JOIN (
  SELECT UserID FROM Table1 GROUP BY UserID HAVING COUNT(*) > 1
) b ON a.UserID = b.UserID