Help with SQL Grouping

Question

A partial fragment of my output looks as follows:

CNEP   P000000025 1
CNEP   P000000029 1
NONMAT P000000029 1
CNEP   P000000030 1
CWHCNP P000000030 1
MSN    P000000030 1

Each row represents a term that a student is in a particular curriculum. Right now I am grouping the information to make sure that each UserID correlates to a partcular curriculum only once.

Notice how "P000000029" and "P000000030" have multiple entries.

I would like to be able to show only those students who have multiple curriculum types within the system.

Answer 1

Assuming the columnbs are named curriculum and userid (no idea what the third column IS;-), you can get the userids of interest via, e.g.:

select userid
from thetable
group by userid
having count(distinct curriculum) > 1

and other info about the userids so selected via in, joins, and similar operations as usual.

Answer 2

I don't think you are showing any student info in your sample data. But you can still use this to find groups with multiples (SQL Server example code, but query will wrok just about anywhere):

DECLARE @YourTable table (col1 varchar(10), col2 char(10), col3 int)

INSERT INTO @YourTable VALUES ('NEP','P000000025',1)
INSERT INTO @YourTable VALUES ('CNEP','P000000029',1)
INSERT INTO @YourTable VALUES ('NONMAT','P000000029',1)
INSERT INTO @YourTable VALUES ('CNEP','P000000030',1)
INSERT INTO @YourTable VALUES ('CWHCNP','P000000030',1)
INSERT INTO @YourTable VALUES ('MSN','P000000030',1)

SELECT
    col1,COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY col1
    HAVING COUNT(col2)>1

OUTPUT

col1       CountOf
---------- -----------
CNEP       2

(1 row(s) affected)