Just a basic SQL question, Finding Dups for a part of a field...
-
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
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
WHERE (ID_NUM = 126366) AND (NOT (REPEAT_FLAG IN ('*', 'R'))) AND (TRANSACTION_STS = 'H') AND (LEFT(CRS_CDE, 8) = LEFT(CRS_CDE, 8))
ORDER BY CRS_CDE
-
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
-
Ok, I edited it to this...
SELECT ID_NUM, TRANSACTION_STS, REPEAT_FLAG,
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, TRANSACTION_STS, REPEAT_FLAG, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
ORDER BY LEFT(CRS_CDE, 8)
Which returns
126366H MAT 1022
But, my problem is I need to see BOTH rows...is there anyway I can get both rows to come back in the select?
-
I am not sure what you mean with 'it only returns rows that has duplicate course codes'.
Can you give a DDL of the table and some examples ?
If you want to see all the '126366 H MAT 102 2' rows repeated 'NumberOfTimesTaken' times, you can do it with the select that follows. But I suppose this is not what you want ? the select will return 2 rows :
126366 H MAT 102 2
126366 H MAT 102 2
select S.ID_NUM, S.TRANSACTION_STS, S.REPEAT_FLAG,LEFT(S.CRS_CDE, 8) AS ADV, C.NumberOfTimesTaken
from STUDENT_CRS_HIST S,
(
SELECT ID_NUM, TRANSACTION_STS, REPEAT_FLAG,
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, TRANSACTION_STS, REPEAT_FLAG, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
) C
where S.ID_NUM = C.ID_NUM
and S.TRANSACTION_STS = C.TRANSACTION_STS
and S.REPEAT_FLAG = C.REPEAT_FLAG
and LEFT(S.CRS_CDE, 8) = C.ADV
-
Journeyman is on the right track here, and I only offer a slight tweak.
The principal Journeyman is showing is that you need to split you original Query into two functional pieces:
1. Identify all students (I'm assuming students are identified by ID_NUM) who have taken a course twice
2. Then using that selection, join it to the main student table to get the data you need for those student/course pairs.
To identify repeated courses, you do need a select like the sub select Journeyman used, but I don't think you need as much data in it. I am also assuming that the selection you have in for a specific student ID is only for testing. The following Subselect will give you ALL students who have taken a course twice:
SELECT ID_NUM, LEFT(CRS_CDE, 8) AS ADV, COUNT(*) As NumberOfTimesTaken
FROM STUDENT_CRS_HIST
WHERE (NOT (REPEAT_FLAG IN ('*', 'R')))
AND (TRANSACTION_STS = 'H')
GROUP BY ID_NUM, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
Now Join this sub-select to the student table:
select S.ID_NUM, C.ADV, C.NumberOfTimesTaken
--And add in any other fields you want from the Studet table
from STUDENT_CRS_HIST S
INNER JOIN (
SELECT ID_NUM, LEFT(CRS_CDE, 8) AS ADV, COUNT(*) As NumberOfTimesTaken
FROM STUDENT_CRS_HIST
WHERE (NOT (REPEAT_FLAG IN ('*', 'R')))
AND (TRANSACTION_STS = 'H')
GROUP BY ID_NUM, LEFT(CRS_CDE, 8)
HAVING COUNT(*) > 1
) C ON S.ID_NUM = C.ID_NUM AND C.ADV = LEFT(S.CRS_CDE,8)
Hope that contibutes to your solution.
-
Folks, Thank you SO much. Both replies were extremely helpful and got me where I needed to be. I appreciate it very much.
Harry C
-
Was that a homework assignment?
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
I guess it was - it was more of an education than an answer sort of along the lines of don't give them food, teach them to fish.
-
No, not at all. I work at a College, and could not figure out the SQL...But nonetheless, I appreciate the help...Thanks again
P.S- is that SQL so simple to figure out that it looks like homework! Lord, i have alot of work to do...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply