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.StudentWholeName
FROM          dbo.StudentInfo
JOIN          dbo.StudentsAssignedToCourse ON dbo.StudentInfo.StudentId = dbo.StudentsAssignedToCourse.StudentId
WHERE         dbo.StudentsAssignedToCourse.ClassScheduleRecordId = '2'
                    and dbo.StudentsAssignedToCourse.SchoolDistrictRecordId='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.
Thanks
bobbellowsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Snarf0001Commented:
Can you post more of the table structure?  Off the top of my head, I'm assuming it's messing up because you're not filtering on the StudentInfo table as well.

Either adding a District filter to the join clause, or filtering as well on StudentInfo.DistrictRecordID would most likely be necessary, but making a lot of assumptions on table structure.

If you can post a diagram or code of the tables, we can help more.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Agree with Snarf that more info on your tables would be a definite help.
But from your description and query I think we can deduce the issues (pretty sure this is the same as what Snarf is getting at, just my take on it).

You are joining the tables on studentID only. This means that if you have 2 students with identical ID's, it will return both records from the studentinfo table, plus for EACH student the matching record from the StudentAssignedToCourse table which matches on StudentID. Your join, as Snarf pointed out already, does not include a link on District so you do not have a unique relationship there.

The procedure doesn't "ignore" the filter on the district. It finds a matching record for the district for 1 of the students, but because your join returns 2 students with the same ID, it's added to both records.

Rewrite to the following:

SELECT	dbo.StudentInfo.StudentWholeName
FROM	dbo.StudentInfo si
		Inner JOIN dbo.StudentsAssignedToCourse sac
			ON si.StudentId = sac.StudentId
			and si.SchoolDistrictRecordId = sac.SchoolDistrictRecordId
WHERE	sac.ClassScheduleRecordId = '2' 
and		sac.SchoolDistrictRecordId='2'
ORDER BY	si.StudentLastName
		,	si.StudentFirstName

Open in new window


This assumes the district column in the StudentInfo and StudentAssignedToCourse tables have the same name. If not, update the column name in the join for the studentinfo table to the correct one.
I've also added aliases to your table names to make the query a bit more readable. This is always good practice.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Snarf0001Commented:
Koen, incredibly detailed response.  Kudos ;)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should provide us with the data you're working with.
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?
0
bobbellowsAuthor Commented:
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

StudentsAssignedToCourseRecId      int
StudentId                                             nvarchar(15)
ClassScheduleRecordId                     nvarchar(15)
SchoolDistrictRecordId                     nvarchar(15)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
bobbellowsAuthor Commented:
Koen,
Executed it. Got this error.

Msg 4104, Level 16, State 1, Procedure SelectStudentsInClass, Line 16
The multi-part identifier "dbo.StudentInfo.StudentWholeName" could not be bound.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
He's using table alias. Replace "dbo.StudentInfo.StudentWholeName" with si.StudentWholeName
0
bobbellowsAuthor Commented:
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
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Sorry about the error, forgot to update the select clause after setting the table aliases.
Since this is a database it's likely that the data will be used in automated programs and reports, in which concatenating strings is really straightforward and doesn't affect the performance. However, if you insist on having the whole name in there, I'd advise to have this updated by a trigger, or by the front end application, when the first and last names are filled or modified. Otherwise, if you ever have to correct a name of an existing student, there's a risk of forgetting to update both the name part as well as the whole name, our a risk of typos when the record is created.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Not really. We're talking about nanoseconds to microseconds difference, something that an human don't have perception of.
And the cons are huge, like explanation above by Koen in the case you'll need to perform an update.
0
bobbellowsAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
bobbellowsAuthor Commented:
Wow! You guys are awesome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.