How do I join these tables ... ?

[Christoph Kiehl]

Hi all,

I don't know if this join is possible with one select statement ...

table1:

id    name
-------------
1    foo
2    bar

table2

id2    id    val
-----------------
1    2    1
2    2    2
1    2    3
1    3    1
2    3    2


Now I need the max value of val together with id2 from table2 joined with
table1 on id where id2=2. This should look like this.

name    id2    val
---------------------
foo    2    3


My problem is how to get the id2 of MAX(val)? Any ideas? Or am I trying
something impossible?

Thanks
Christoph