I have been tasked with developing a database with Stored Procedures that will hold information of students from four School Districts in the region. The complicating factor is some students in different districts have identical Student ID numbers. I set up the table to include a field to distinguish the district. Therefore when searching 2 criteria have to be met, Student (or Course) ID and District ID.
One of the tables that is required is a list of courses being taught. Students are then assigned to the courses. The courses also have to be unique even though the districts are teaching same subjects (ie Algebra, Calculus, etc) . One of the SP's I'm supposed to develop is to list the names of the students in a particular course. Each course is also identified by the same district code as the students of a particular district. One requirement I must satisfy is to develop a list of students' names in a particular course in a particular district by the student's ID numbers.
I thought I had everything ready to go until I tested it out with a little (very little) data. I only have four students in the DB. There are two pairs having the same Student ID but different District Id numbers.
Below is the SP. When it is executed for Class 1 it returns results for two students who have the same Student ID number but different District ID numbers -- it seems to ignore the district record filter. Only one of the students is registered in Class 1.
When it is executed for Class 2 I get the other 2 students with common ID numbers even though they have different District ID numbers and only one is registered in the course. Again it seems to ignore the District record filter.
JOIN dbo.StudentsAssignedToCourse ON dbo.StudentInfo.StudentId = dbo.StudentsAssignedToCourse.StudentId
WHERE dbo.StudentsAssignedToCourse.ClassScheduleRecordId = '2'
ORDER BY dbo.StudentInfo.StudentLastName, dbo.StudentInfo.StudentFirstName
I'm supposed to have this all done so the person who is doing the Web App can wire it up for school startup in August. Any help you can give will be appreciated.