sql advice

[Ruslan Zasukhin]

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

Hi Chris,

> 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

I think first of all it can be good idea to use SQL92 syntax for joins,
Although it looks horrible...

Also, since you have so complex query, do not write it as the whole.
Grow it step by step and check results.
  
-----------------
SELECT ...
FROM 
  (Teachers FULL OUTER JOIN Classes ON Teachers.RecID = Classes.TeacherPtr)


---------------------
SELECT ...
FROM 
  ((Teachers FULL OUTER JOIN Classes ON Teachers.RecID = Classes.TeacherPtr)
    LEFT OUTER JOIN StudentClass ON Classes.RecID = StudentClass.ClassPtr)


But looking at your structure above:

1)  StudentYear => Student

    I think here should be INNER JOIN, but in your query it is FULL OUTER.
    

2) StudentClass is a cross table. Okay.
    then probably its links also should be INNER JOINS.


-------------------
Logic of your query is:

  * you have set of Students (for 2005 year).
        We can forget about StudentYear now.

  * and you have set of Teachers (active).

  * and you have Classes.
        Linked to Students as M : M
        Linked to Teachers as M : 1


This is high level diagram of your db structure:

   Teachers      Classes        StudentYear      Students
            link1        link2             link3


Note, link1 is in your case table StudentClass. It could by BinaryLink.
Right? IF to think in terms of link it is simpler to see the whole picture.

Now we can start draw draft of JOIN:

FROM 
    (((Teachers JOIN Classes using link1)
        JOIN StudentYear USING link2)
            JOIN Students USING link3 )

Looks much simpler, right? Btw this is where advantage of LINKS is visible
also by eye. 

Now let's try expand link1 and link3 according to your structure, they are
simple:

FROM 
    (((Teachers t JOIN Classes c ON t.recID = c.TeacherPtr )
        JOIN StudentYear sy ON link2 )
            JOIN Students s USING sy.StudentPtr = s.recid )

And now we need expand link2:

FROM 
    (((Teachers t JOIN Classes c ON t.recID = c.TeacherPtr )
        JOIN StudentClass scl ON c.recID = scl.ClasPtr
        JOIN StudentYear sy ON scl.StudentYearPtr = sy.recID )
            JOIN Students s USING sy.StudentPtr = s.recid )

I hope this is close to true. [Not tested].

IF you need OUTER JOIN, then I think you need it between
    Teachers and Classes.


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