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).