bobbellows
asked on
Filter on Stored Procedure with JOIN not working at all.
Dear Experts,
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.
SELECT dbo.StudentInfo.StudentWho
FROM dbo.StudentInfo
JOIN dbo.StudentsAssignedToCour
WHERE dbo.StudentsAssignedToCour
and dbo.StudentsAssignedToCour
ORDER BY dbo.StudentInfo.StudentLas
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.
Thanks
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.
SELECT dbo.StudentInfo.StudentWho
FROM dbo.StudentInfo
JOIN dbo.StudentsAssignedToCour
WHERE dbo.StudentsAssignedToCour
and dbo.StudentsAssignedToCour
ORDER BY dbo.StudentInfo.StudentLas
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.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Snarf0001
Koen, incredibly detailed response. Kudos ;)
Thanks Snarf. But if accepted as a solution would recommend splitting the points, as it's basically the same as what you already suggested. Just more detailed.
You should provide us with the data you're working with.
Also I'm concerned with your database design:
Also I'm concerned with your database design:
Why are you using chars for your ID? Why '2' and not 2?
Why do you have a StudentWholeName and StudentFirstName and StudentLastName? Would you have also a StudentMiddleName?
ASKER
Thanks for your insight. I'm a Government/Econ teacher. I have a little computer training -- basic fundamentals-- so they gave me this task. Koen, I'll try out your solution as soon as I finish this. Below are the tables. Knowing how these people think they will want to expand the StudentInfo table in the future to include Gender, DOB, etc.. But this is all they asked from me right now. Again, thanks.
Bob Bellows
StudentInfoTable:
StudentInfoRecordId int
SchoolDistrictRecordId nvarchar(15)
StudentId nvarchar(16)
StudentFirstName nvarchar(25)
StudentMiddleName nvarchar(25)
StudentLastName nvarchar(25)
StudentWholeName nvarchar(80)
StudentAssignedToCourse table
StudentsAssignedToCourseRe
StudentId nvarchar(15)
ClassScheduleRecordId nvarchar(15)
SchoolDistrictRecordId nvarchar(15)
Bob Bellows
StudentInfoTable:
StudentInfoRecordId int
SchoolDistrictRecordId nvarchar(15)
StudentId nvarchar(16)
StudentFirstName nvarchar(25)
StudentMiddleName nvarchar(25)
StudentLastName nvarchar(25)
StudentWholeName nvarchar(80)
StudentAssignedToCourse table
StudentsAssignedToCourseRe
StudentId nvarchar(15)
ClassScheduleRecordId nvarchar(15)
SchoolDistrictRecordId nvarchar(15)
Once again, why having nvarchar ID's? They'll store alphanumeric characters? If not, leave them as integers.
And you don't need the WholeName, don't you? It's only a concatenation of StudentFirstName + StudentMiddleName + StudentLastName.
All these changes can save you more than 100 bytes per student record and will make your queries run faster.
And you don't need the WholeName, don't you? It's only a concatenation of StudentFirstName + StudentMiddleName + StudentLastName.
All these changes can save you more than 100 bytes per student record and will make your queries run faster.
ASKER
Koen,
Executed it. Got this error.
Msg 4104, Level 16, State 1, Procedure SelectStudentsInClass, Line 16
The multi-part identifier "dbo.StudentInfo.StudentWh
Executed it. Got this error.
Msg 4104, Level 16, State 1, Procedure SelectStudentsInClass, Line 16
The multi-part identifier "dbo.StudentInfo.StudentWh
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Victor,
You're right about keeping the parameters as integers. It was an oversight on my part and I hadn't had a chance to respond to your initial comment.
You're also right about the concatenation to create the WholeName. As I mentioned before I was tasked with this because I had the most -- even though it wasn't very much -- experience with MS SQL Databases and I'm really a Government/Econ teacher. Around here you can be tasked with anything you might have even the slightest bit of knowledge about. I tried to explain that a little bit of knowledge is dangerous.
That being said, this is what I was thinking about storing/calling WholeName. I look forward to your expert input. Yes, it's a storage concern. Especially since this could have thousands of records. However, if the users needed to display a whole name very often I thought it would be faster and less load on the server to do the queries on the integers and retrieve the WholeName rather than do the queries and run the concatenation each time. This class roster page could have as many as 35 students. Each one would have to be concatenated each time they called up the roster. I was looking at what the users might want to do in the future with the app they are developing. I'm sure this is a work in progress and I'm going to be dealing with this group (I affectionately call them "The Beta Testers") in the future. I look forward to your response.
Bob Bellows
You're right about keeping the parameters as integers. It was an oversight on my part and I hadn't had a chance to respond to your initial comment.
You're also right about the concatenation to create the WholeName. As I mentioned before I was tasked with this because I had the most -- even though it wasn't very much -- experience with MS SQL Databases and I'm really a Government/Econ teacher. Around here you can be tasked with anything you might have even the slightest bit of knowledge about. I tried to explain that a little bit of knowledge is dangerous.
That being said, this is what I was thinking about storing/calling WholeName. I look forward to your expert input. Yes, it's a storage concern. Especially since this could have thousands of records. However, if the users needed to display a whole name very often I thought it would be faster and less load on the server to do the queries on the integers and retrieve the WholeName rather than do the queries and run the concatenation each time. This class roster page could have as many as 35 students. Each one would have to be concatenated each time they called up the roster. I was looking at what the users might want to do in the future with the app they are developing. I'm sure this is a work in progress and I'm going to be dealing with this group (I affectionately call them "The Beta Testers") in the future. I look forward to your response.
Bob Bellows
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Koen, Snarf0001, Vitor (sorry I misspelled your name above)
OK. Executed the SP with the latest and it worked! Thanks all of you. Now -- I don't know how to split the points -- In the beginning Snarf identified the basic problem Koen suggested a points split. Koen helped out with the rewrite. Vitor caught Koen's error. Then both Koen and Vitor helped me understand about the concatenating of strings. Wow! You guys are awesome. Thanks. Any suggestions on how to split this up?
Bob Bellows
OK. Executed the SP with the latest and it worked! Thanks all of you. Now -- I don't know how to split the points -- In the beginning Snarf identified the basic problem Koen suggested a points split. Koen helped out with the rewrite. Vitor caught Koen's error. Then both Koen and Vitor helped me understand about the concatenating of strings. Wow! You guys are awesome. Thanks. Any suggestions on how to split this up?
Bob Bellows
Just split the points as you think is correct. The correct answer should be the one that lead you to the solution, so should get more points, and the other ones should be marked as assisted solution and you should give accordingly to how much they helped you to clarify the problem.
ASKER
Wow! You guys are awesome.