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.
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.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
HLRosenberger
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
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_
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_
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
SELECT [id] ,[teacherrank] FROM [tdsdev].[dbo].[classroom_
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_
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
ASKER
Thanks!
Open in new window