By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

History query question

P: 1
I tried to explain the best I can but it is very difficult to do. Here it goes.

I have three history tables. Teacher, student and school. School and teacher, and school and student are linked together.
I need to make a query that makes in one query a temporary table that list all the students that had a teacher change at some given date in the past.

For example:
If I give the query the following date 01/01/2007 it needs to give me all the student that had a teacher change.
But the table history teacher doesn't have a link with the history student table. So I need to search the history school table to find the student id.

There are three things that can happen:
1st: I give the date 01/01/2007 in so I need to look for this date in the history teacher table. If the school id is not null the teacher hasn't been deleted. Than I need to now if it change from school. I need to do that by searching the change that happened before the date I gave up. If the school id's are identical the teacher is still at the same school. So I only need to look for the first teacher id in the history school table to get the student id.

2nd: If the school id did change I have to look for the first and second teacher id in the history school table to get the student id.

3rd: If the school id is null I need to look for the second teacher id in the history school table.

Sorry for my bad English

Thanks in advance

Nyh
Aug 31 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.