join question

[Claudius Sailer]

Am Samstag, 28.06.03, um 22:15 Uhr (Europe/Berlin) schrieb Claudius 
Sailer:

> Hi Charles,
>
>
> Am Samstag, 28.06.03, um 21:34 Uhr (Europe/Berlin) schrieb Charles 
> Yeomans:
>
>>> SELECT teacher.*
>>> FROM teacher t, jointable j1, jointable j2
>>> WHERE t.RecID=j1.teacherptr
>>> AND t.RecID=j2.teacherptr
>>> AND j1.studentptr=1
>>> AND j2.studentptr=2
>>>
>>> is this a runnable way?
>>>
>>
>> This will return 0 results.  I'm thinking that it's some sort of 
>> inner or outer join, but I've no experience with them.
>
> *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