First of all let me just say I'm a newb but have tried and googled for awhile. I work for a school district and am trying to tweak some stored procedures on our student info system, here is my problem I have a stored procedure that exports student records out for various purposes.
We need to include teacher homeroom numbers in the file and there is a table for that however due to some archaic data placement it has the teachers id number now I can get there room number from another table but I just can't figure out how to populate there room number multiple times to the correct student roughly 160 times it would need to populate. I know I need to use a union but just don't know where to go from there.
Again I apologize if this is a dead simple question I have had to pick up after our data guy left and I know enough to be dangerous.
Ok that makes since, I did a sample script test if it would work and it returns multiple student records with different room numbers even though all teachers only have one room number assigned to them. When I don't use the join and simply pull the student data out it lists one student per row. Any thoughts?
/* SQL Homeroom Test Scritpt*/
SELECT DISTINCT TOP 100
WHERE Student_Info_Core.Student_Status_A_W_P='A' AND
ORDER BY Student_ID
Ok I figured it out, apparently our SIS never drops a teacher from the DB it simply creates new ones each year, with with a new school year in the row along with a different room number if they have moved to a new class since last year. That's why it was returning multiple room numbers for each teacher I simply used the line below to filter it out.
Thanks for the help! Sorry after solving this I realize this was noob sauce question!