Link to home
Create AccountLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

Help with SQL

I have a classroom table.  It has a primary key column, along with primary teacher Foreign key,  secondary teacher Foreign key and tertiary teacher Foreign key.  Each of the teacher FKs are keys to a user table.

A teacher could be primary in one room, secondary in another and tertiary in a third.  So, I need a query that returns the Primary Key of the classroom for a teacher, based on the primary, secondary and tertiary hierarchy.   For this example I would want the Primary Key of the classroom where they are the primary teacher.  

If the teacher was not a primary teacher in any classroom, but was secondary teacher in one room and tertiary in a second room, then I need the Primary Key of the classroom where they are the secondary teacher.  

classroom Data examples

classroom ID       FKPrimary       FKSecondary         FKTertiary
1                                  100                    NULL                     NULL
2                                  200                     1234                     400
3                                  300                     400                       500
4                                  400                     300                       200  
5                                  500                     400                       4444
                               

For teacher 100, I need classroom ID of 1.
For teacher 200, I need classroom ID of 2.
For teacher 300, I need classroom ID of 3.
For teacher 400, I need classroom ID of 4.
For teacher 500, I need classroom ID of 5.

For teacher 1234, I need classroom ID of 2.
For teacher 4444, I need classroom ID of 5.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

SELECT c.classroomID, c.FKPrimary as teacherID
FROM classroom c
INNER JOIN teacher t1 ON c.FKPrimary=t1.teacherID
UNION 
SELECT c.classroomID, c.FKSecondary 
FROM classroom c
INNER JOIN teacher t2 ON c.FKSecondary = t2.teacherID
UNION 
SELECT c.classroomID, c.FKTertiary
FROM classroom c
INNER JOIN teacher t3 ON c.FKTertiary= t3.teacherID

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I'm sorry, didn't have time to test my solution but Brian's made me rethink on it so here's a new version from me:
SELECT c.classroomID, c.PrimaryTeacherID as teacherID
FROM Classroom c
INNER JOIN Teacher t1 ON c.PrimaryTeacherID=t1.teacherID
UNION 
SELECT c.classroomID, c.SecondaryTeacherID 
FROM Classroom c
INNER JOIN Teacher t2 ON c.SecondaryTeacherID = t2.teacherID
WHERE NOT EXISTS (SELECT 1 
		FROM Classroom c1
		WHERE c1.PrimaryTeacherID = c.SecondaryTeacherID)
UNION 
SELECT c.classroomID, c.TertiaryTeacherID
FROM Classroom c
INNER JOIN Teacher t3 ON c.TertiaryTeacherID = t3.teacherID
WHERE NOT EXISTS (SELECT 1 
		FROM Classroom c1
		WHERE c1.PrimaryTeacherID = c.TertiaryTeacherID)

Open in new window

Avatar of HLRosenberger

ASKER

Expanding on Brian's idea of Teacher Rank column, I created a view shown below called "classroom_teachers".   Then I use this Query:

SELECT  [id] ,[teacherrank]  FROM [tdsdev].[dbo].[classroom_teachers]
 WHERE FK_user = 278
 ORDER BY teacherrank asc

Then this returns all classroom IDs for a teacher, where the first one is the top one in the hierarchy.   Also if I add TOP 1, then I get the single record that I want:

  SELECT  TOP 1 [id] ,[teacherrank]  FROM [tdsdev].[dbo].[classroom_teachers]
 WHERE FK_user = 278
 ORDER BY teacherrank asc



classroom_teachers  VIEW:
__________________________________________________________________

SELECT id, FK_primary_teacher AS FK_user, 1 AS teacherrank
FROM     tch_admin_classroom
UNION
SELECT id, FK_secondary_teacher AS FK_user, 2 AS teacherrank
FROM     tch_admin_classroom
UNION
SELECT id, FK_tertiary_teacher AS FK_user, 3 AS teacherrank
FROM     tch_admin_classroom
Thanks!