join question

[Charles Yeomans]

On Saturday, June 28, 2003, at 03:22 PM, Claudius Sailer wrote:

>
> Am Samstag, 28.06.03, um 20:20 Uhr (Europe/Berlin) schrieb Stephan 
> Huber:
>
>> Sorry to insist, but this is the same solution I propagated and not 
>> the one Charles is looking for ;-)
>>
>> say we have three tables
>>
>> teacher      student
>> A            X
>> B            Y
>> C            Z
>>
>> jointable:
>> teacherptr studentptr
>> A          X
>> A          Z
>> B          X
>> B          Y
>>
>> A has students X and Z, B has X and Y.
>>
>> the result of your and mine query would be:
>>
>> SELECT teacher.* FROM teacher, jointable WHERE (teacher.recID = 
>> jointable.teacherPtr) and ((jointable.studentPtr = 1) or 
>> (jointable.studentPtr = 2))
>>
>> 'A, B'
>>
>> The correct result which Charles is looking for, is 'B' because only 
>> teacher B has students X AND Y.
>>
>> You see the problem?
>
> Yes I see. This is really hard
>
> Try
>
> 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.

Charles Yeomans