join question

[Claudius Sailer]

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?

bye

Claudius