join question

[Stephan Huber]

--On Samstag, 28. Juni 2003 19:59 Uhr +0200 Claudius Sailer 
<Claudius at sailer-online.de> wrote:

> SELECT t1.field1, t1.field2, t2.field3
> FROM table1 t1, table2 t2
> WHERE t1.RecID=t2.Teacher
> AND (t2.student=1 OR t2.student=2)
>
> This is an example and it should work, but the relations could be not
> correct. Play with it ;-))

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?

Stephan