Hi,
here are two tables (with test values), I want to query:

Table 1
Master Child Level ResVal
a a 0 r1
a b 1 r1
a c 1 r1
a d 2 r1
b b 0 r2
b d 1 r3
c c 0 r4

Table 2
User Group
U1 a
U1 b


query:
select child, resVal, Level
from Table2 U
join table1 A on A.Master=U.Group

Result:
Child ResVal Level
a r1 0
b r1 1
b r2 0
c r1 1
d r3 1
d r1 2

I want to "filter" this result, so that I get only those lines, that, for each child, have the lowest level.

In SQL-terms:

select child, resval
from result
where level=min(level)
group by child

Currently my query looks like this:

select A.Child, A.ResVal
from Table2 U
join Table1 A on A.Master=U.Group
where (select min(A2.Level) from Table2 U2
join Table1 A2 on A2.Master=U.Group
where A2.Child = A.Child
group by A2.Child) = A.Level

This seems quite complicated to me, since I have to repeat the complete join in the where-clause, when all I want is to find the grouped minima for one field.
Is there any way to write this query in a simpler way (escpecially, since the real query takes some more tables into the join (or both joins))?
What does the query optimizer (MS-Sql 2000) say to a query like this? Does it recognize, that the where clause contains the same join an reuse it?

I hope I stated my case clear enough
Thanks
Lutz