Results 1 to 6 of 6

Thread: Some Union Help

  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Some Union Help

    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.

    Thanks
    Alex

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a join, not a union
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    22
    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?

    Code:
    /* SQL Homeroom Test Scritpt*/
      
      SELECT DISTINCT TOP 100 
      Student_Info_Core.[School_Number],
      Student_Info_Core.[Student_ID],
      Student_Info_Core.[First_Name],
      Student_Info_Core.[Last_Name],
      Student_Info_Core.[Grade_level],
      Student_Info_Core.[Homeroom_Teacher],
      Teacher.[Teacher_ID],
      Teacher.[Teacher_First_Name],
      Teacher.[Teacher_Last_Name],
      Teacher.[Teacher_Room]
      
      FROM Student_Info_Core
      JOIN Teacher
      
      ON Student_Info_Core.Homeroom_Teacher=teacher.Teacher_ID
      
      WHERE Student_Info_Core.Student_Status_A_W_P='A' AND 
      Student_Info_Core.School_Number='0021'
      
      ORDER BY Student_ID

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you must remember that i don't know your data

    the multiples have gotta be coming from somewhere, but you did not explain your PK-FK relationships, so...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2011
    Posts
    22
    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.

    Code:
    Where Teacher.School_Year='1112'
    Thanks for the help! Sorry after solving this I realize this was noob sauce question!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the update, that makes sense

    you can safely forget about the PK-FK stuff for the time being

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •