Help with SQL

HLRosenberger
HLRosenberger used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Database Administrator
Top Expert 2005
Commented:
DECLARE @Teacher TABLE
(
	TeacherID	INT
);

DECLARE @Classroom TABLE
(
	ClassroomID	INT IDENTITY(1,1),
	PrimaryTeacherID	INT,
	SecondaryTeacherID	INT,
	TertiaryTeacherID	INT
);

INSERT INTO @Teacher
VALUES (100),
	(200),
	(300),
	(400),
	(500),
	(1234),
	(4444);

INSERT INTO @Classroom (PrimaryTeacherID, SecondaryTeacherID, TertiaryTeacherID)
VALUES (100, NULL, NULL),
	(200, 1234, 400),
	(300, 400, 500),
	(400, 300, 200),
	(500, 400, 4444);

WITH cteTeacher AS
(
	SELECT ClassroomID, 1 AS TeacherRank, PrimaryTeacherID AS TeacherID
	FROM @Classroom
	WHERE PrimaryTeacherID IS NOT NULL

	UNION ALL

	SELECT ClassroomID, 2 AS TeacherRank, SecondaryTeacherID
	FROM @Classroom
	WHERE SecondaryTeacherID IS NOT NULL

	UNION ALL

	SELECT ClassroomID, 3 AS TeacherRank, TertiaryTeacherID
	FROM @Classroom
	WHERE TertiaryTeacherID IS NOT NULL
),
cteTeacherClassroom AS
(
	SELECT TeacherID, ClassroomID,
		ROW_NUMBER() OVER(PARTITION BY TeacherID ORDER BY TeacherRank) AS RowNumber
	FROM cteTeacher
)
SELECT TeacherID, ClassroomID
FROM cteTeacherClassroom
WHERE RowNumber = 1

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Author

Commented:
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

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial