sql advice

[Ruslan Zasukhin]

On 1/23/06 10:41 PM, "Chris Sheffield" <cmsheffield at gmail.com> wrote:

Hi Chris,
 
> 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.

Mmm,  why you expect to see something from 2004 year ?

You have made WHERE = 2005

> 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.

Then I have not catch. You want to see 2005 year only ?
Or prev years also ?

> 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. :-)

:-)) 

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]