sql advice

[Chris Sheffield]

I need some help with a database query.  Hope it's okay that I post  
this here.

I'm using Valentina 1 with Revolution.

I have five tables: Students, StudentYear, StudentClass, Classes, and  
Teachers.  Students contains data that is pretty much static.  It  
does not change from school year to school year.  StudentYear  
contains a pointer to Students, along with a couple fields of data  
that change every school year (Grade, SchoolYear, etc.).   
StudentClass is a cross table (or whatever the proper term is)  
between StudentYear and Classes to create a many-to-many  
relationship.  And then Classes contains an object pointer to  
Teachers to create a one-to-many between Teachers and Classes.   
Hopefully this all makes sense so far.

Now, in my program I have to generate a list of teachers, such that  
all teachers are listed, along with any classes that point to those  
teachers (if any), along with any students associated with those  
classes (if any).  Here is the query I'm currently running:


SELECT Teachers.FirstName, Teachers.LastName, Classes.ClassName,  
Classes.Reading, Students.FirstName, Students.LastName,  
StudentYear.Grade, Teachers.Type, Teachers.RecID FROM Teachers,  
Classes, Students, StudentClass, StudentYear WHERE  
Classes.TeacherPtr*=*Teachers.RecID AND  
StudentClass.ClassPtr*=Classes.RecID AND  
StudentClass.StudentYearPtr*=StudentYear.RecID AND  
StudentYear.StudentPtr*=*Students.RecID AND  
StudentYear.SchoolYear='2005' AND Teachers.Active=1


As you can see, it's fairly involved.  SchoolYear is a value that  
would correspond to the current school year, so that when running the  
query it should return all data corresponding to the current school  
year.  Unfortunately, it's not working correctly.  I'm getting most  
of the correct data, but some is missing.  What's happening is any  
teachers and classes that had students assigned to them in a previous  
school year do not show up in the list for the current school year.   
I have tried every possible combination of joins I can think of to  
make this work and I can't figure it out.  Maybe what I'm trying to  
do is not even possible, though it seems like it should be.  If I  
omit the "StudentYear.SchoolYear='2005'" part, then of course I get  
all the data from every school year, but that's not what I want.

Hopefully I've provided enough information.  If anyone can see  
anything I'm missing, I would greatly appreciate any help you could  
give.  I've been banging my head on my desk now for several hours,  
and it's really starting to hurt. :-)

Thanks,
Chris


------------------------------------------
Chris Sheffield
Read Naturally
The Fluency Company
http://www.readnaturally.com
------------------------------------------