How to select all fields for duplicate records?

[pchalis]

I have a table called Transfer which looks like this:

Org_id Subject Crs_nbr Trnsfr_subject Trnsfr_Crs_nbr

00111 ACCT 101 ACC 1001
00111 ACCT 101 ACC 1001
00111 ACCT 101 BUS 1001
00222 BIOL 210 BIOL 2101
00222 CHEM 210 CHE 2101
00333 CHEM 210 CHE 2101
00333 MATH 101 MAT 1001

I am trying to pull out all the information(coulumns) for those records where Subject, and Crs_nbr occure more than once for each Org_id.

So the output will be something like this:

Org_id Subject Crs_nbr Trnsfr_subject Trnsfr_Crs_nbr

00111 ACCT 101 ACC 1001
00111 ACCT 101 ACC 1001
00111 ACCT 101 BUS 1001

Any help would be highly appreciated.

 

[PHV]

SELECT A.*
FROM Transfer A INNER JOIN (
SELECT Org_id, Subject, Crs_nbr FROM Transfer
GROUP BY Org_id, Subject, Crs_nbr HAVING COUNT(*) > 1
) B ON A.Org_id = B.Org_id AND A.Subject = B.Subject AND A.Crs_nb = B.Crs_nb

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pchalis]

PVS, thanks a lot.
That worked.