Pretty Basic Dup record question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pretty Basic Dup record question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-02 : 14:46:14

Ok, I know this has to be simple, so I apoplogize ahead of time. I need to modify this simple select so that it only returns rows that has duplicate course codes. Basically, I want to see all student that have taken ANY classes twice....Thanks

One thing I forgot to mention. These are not true duplicate rows. The only part that needs to be a dup is PART of the CRS_CDE column... (LEFT(CRS_CDE, 8) that is the only part that needs to match...Thanks

Harry C

This query returns one line...telling me that there is a dup. But I need to return BOTH lines in the SQL Query. Is there a way I can get both lines for this student back in the select? Thanks

SELECT ID_NUM, YR_CDE, TRM_CDE, CRS_CDE, TRANSACTION_STS, REPEAT_FLAG, GRADE_CDE,
LEFT(CRS_CDE, 8) AS ADV, COUNT(*) As NumberOfTimesTaken
FROM STUDENT_CRS_HIST
WHERE (ID_NUM = 126366)
AND (NOT (REPEAT_FLAG IN ('*', 'R')))
AND (TRANSACTION_STS = 'H')
AND (LEFT(CRS_CDE, 8) = LEFT(CRS_CDE, 8))
GROUP BY ID_NUM, YR_CDE, TRM_CDE, CRS_CDE, TRANSACTION_STS, REPEAT_FLAG, GRADE_CDE, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
ORDER BY CRS_CDE

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 14:57:27

[code]
SELECT * FROM STUDENT_CRS_HIST o
WHERE EXISTS (
SELECT ID_NUM
, YR_CDE
, TRM_CDE
, CRS_CDE
, TRANSACTION_STS
, REPEAT_FLAG
, GRADE_CDE
, LEFT(CRS_CDE, 8) AS ADV
FROM STUDENT_CRS_HIST i
WHERE ID_NUM = 126366
AND NOT (REPEAT_FLAG IN ('*', 'R'))
AND TRANSACTION_STS = 'H'
AND LEFT(CRS_CDE, 8) = LEFT(CRS_CDE, 8) -- This makes no sense
AND o.ID_NUM = i.ID_NUM
AND o.YR_CDE = i.YR_CDE
AND o.TRM_CDE = i.TRM_CDE
AND o.CRS_CDE = i.CRS_CDE
AND o.TRANSACTION_STS = i.TRANSACTION_STS
AND o.REPEAT_FLAG = i.REPEAT_FLAG
AND o.GRADE_CDE = i.GRADE_CDE
AND LEFT(o.CRS_CDE, 8) = LEFT(i.CRS_CDE, 8)
GROUP BY ID_NUM
, YR_CDE
, TRM_CDE
, CRS_CDE
, TRANSACTION_STS
, REPEAT_FLAG
, GRADE_CDE
, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
)

[/code]

Brett

8-)

Harry C
Posting Yak Master

148 Posts

Posted - 2005-02-02 : 15:11:47

Thanks, this is what I ended up with and that seems to work. I appreciate the help...

SELECT ID_NUM,
YR_CDE,
TRM_CDE,
CRS_CDE,
TRANSACTION_STS,
GRADE_CDE,
REPEAT_FLAG,
REPEAT_COUNT,
LEFT(CRS_CDE, 8) FROM STUDENT_CRS_HIST o
WHERE EXISTS (
SELECT ID_NUM
, REPEAT_FLAG
, LEFT(CRS_CDE, 8)
FROM STUDENT_CRS_HIST i
WHERE NOT (REPEAT_FLAG IN ('*', 'R'))
AND TRANSACTION_STS = 'H'
AND o.ID_NUM = i.ID_NUM
AND o.REPEAT_FLAG = i.REPEAT_FLAG
AND LEFT(o.CRS_CDE, 8) = LEFT(i.CRS_CDE, 8) -- The part that made no sense is changed to this..
GROUP BY ID_NUM
, REPEAT_FLAG
, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
)
ORDER BY CRS_CDE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources