We help IT Professionals succeed at work.
Get Started

Help with SQL

Last Modified: 2015-12-09
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.
Watch Question
Database Engineer
Top Expert 2005
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE