sql advice

[Ruslan Zasukhin]

On 1/24/06 12:56 AM, "Chris Sheffield" <cmsheffield at gmail.com> wrote:

> When I do this (in Valentina 1 syntax (hopefully I've got it
> correct), I don't get all the data I want.  Any classes and teachers
> that do not have students in the current year do not show on the
> list.  And they should.
> 
> Maybe I've got the syntax on my joins incorrect still.  Here it is:
> 
> Classes.TeacherPtr=Teachers.RecID AND
> StudentClass.ClassPtr=Classes.RecID AND
> StudentClass.StudentYearPtr=StudentYear.RecID AND
> StudentYear.StudentPtr=Students.RecID
> 
> 
> Am I still missing something?

This is only inner joins. You need some of them to be outers.

So you want to see ALL Classes,
    even if they not have student in current year.

    Note, I change order of conditions to reflect your diagram!
    You start from Teacher, then go to Classes.
    From Classes to you go Students via link-table StudentClass.


Teachers.RecID = Classes.TeacherPtr AND
Classes.RecID *= StudentClass.ClassPtr AND
StudentClass.StudentYearPtr = StudentYear.RecID AND
StudentYear.StudentPtr = Students.RecID


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