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.
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Brian CroweDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

HLRosenbergerAuthor 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
HLRosenbergerAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.