David Cameron wrote:
> You can perform this logic in a join but there is (or was for me) a lot 
of
> thinking in trying to get the joins set up and query is slow. Meet you 
new
> best friend, sub queries.
> 
> SELECT t.idteacher, t.age, rt.opinion
> FROM teachers t
>     LEFT OUTER JOIN rt ON
>     t.idteacher=rt.idteacher
> WHERE EXISTS (SELECT NULL
>              FROM rteacherstudent
>              WHERE idstudent='Thomas'
>                  AND idteacher=rt.idteacher)
> 
> Effectively what happens is that the subquery builds a list of teachers 
that
> the student has. You then select the information you need for each of the
> teachers in that list.
> 
> Also you will find a speed gain if you create a temp table and drop the 
list
> of teachers into the temp table, then run the select query on the temp
> table. The reason is that with the current statement the subquery is 
being
> run for each row, but would be run only once if a temp table was used. 
Then
> you would be performing a less expensive query on the temp table for each
> row.

In this case, the query would be more logically structured as:

 SELECT t.idteacher, t.age, rt.opinion
 FROM teachers t
     LEFT OUTER JOIN rteacherstudent rt ON
     t.idteacher=rt.idteacher
 where rt.idstudent = 'Thomas'

rather than using a subquery with EXISTS, since you've joined the tables 
anyway. However, the original poster wanted a way to get either Thomas' 
rating, or NULL, for each teacher. In that case, the logical option is to 
join to a derived table:

 SELECT t.idteacher, t.age, rt.opinion
 FROM teachers t
     LEFT OUTER JOIN (select * from rteacherstudent
                      where idstudent = 'Thomas') rt ON
     t.idteacher=rt.idteacher

This is essentially the same as your suggestion of a temp table, except 
that you don't have to build a temp table, and you're joining instead of 
subquerying it in a WHERE clause.

--Wilson