join question

[Ruslan Zasukhin]

on 6/28/03 23:52, Claudius Sailer at Claudius at sailer-online.de wrote:

>> *hmmmm* I don't have my documents here and also less inner-outer-join
>> experiences, so you have to wait until user with better SQL know how
>> will help you.
> 
> Sorry but I tested it now here, and it worked with viSQL
> 
> 
> Select RecID, * FROM Teacher
> 
> RecID    Name
> 1    A
> 2    B
> 3    C
> 
> 
> Select RecID, * FROM Student
> 
> RecID    Name
> 1    X
> 2    Y
> 3    Z
> 
> Select RecID, * FROM JoinTable
> 
> RecID    TeacherPtr    StudentPtr
> 1    1    1
> 2    1    2
> 3    2    1
> 4    2    3
> 
> 
> SELECT t.*
> FROM Teacher t, JoinTable j1, JoinTable j2
> WHERE t.RecID=j1.TeacherPtr
> AND t.RecID=j2.TeacherPtr
> AND j1.StudentPtr=1
> AND j2.StudentPtr=3
> 
> RESULT --> B

VERY beautiful solution, Claudius !!!  :-)
This way as we can see require usage of JOIN TABLE few times in query.

Another way resolve this problem, I think is UNION of each result.
Also not very fast way.

Interesting task. I wonder that in SQL there is no special term for this,
which allow DBMS execute this task more effective.
I need think about this in future.

May be here can be used IN operator...
No, IN is analog of OR.

Must be in the SQL operator that is analog of EACH.

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------