Join Problem..........

Hi,
I need help. I have one Parent table P1 and a Child Table C1. I have 3 records in table P1 and 9 records in C1 (3 records for each records of P1).

When I am doing the inner join of these tables i am getting 9 records, where as actually I want only 3 records. I need all 3 rows from P1 and one row each from the C1 against the corresponding rows of P1. Single row from C1 will come from the criteria based on the Date column of the C1 table. Like the row that will be selected from the table C1 for the row from tbale P1 will have the MAX(DATE) value among all the rows in it C1).

By inner join i am able to extract all the 3 rows where as i need only the row that contains the MAX(DATE).

Kindly help me in this regard.

Thanks,
Rahul Jhaselect P1.foo
, P1.bar
, M.qux
, M.date
from P1
inner
join C1 as M
on M.flim = P1.flam
and M.date =
( select max(date)
from C1
where flim = P1.flam )|||Thanks. :-)

This Will Work. Donno y this din click in my mind.

Thnaks Once Again

Rahul Jha