sql advice

[Chris Sheffield]

Thanks, Ruslan.  But isn't this sql syntax only supported with  
Valentina 2, or am I mistaken?


On Jan 23, 2006, at 2:27 PM, Ruslan Zasukhin wrote:

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

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