Basic Question on Joins

[dilipananth1984]

Hi All,

I have a 2 tables namely Employee and reports. An employee will/can have multiple reports associated with him for a particular month. I need to fetch the the employee details and the last (or latest) report details for that month.

If for e.g Employee table has 100 entries, i will have (1000 * 12 month * 30 reports/month) 360,000 entries in the reports table. So my query should return only 1000 records (for each employee the latest report for that month). How can i do this without using cursor?

thanks,
Dilip

[azimmer]

Hi All,

I have a 2 tables namely Employee and reports. An employee will/can have multiple reports associated with him for a particular month. I need to fetch the the employee details and the last (or latest) report details for that month.

If for e.g Employee table has 100 entries, i will have (1000 * 12 month * 30 reports/month) 360,000 entries in the reports table. So my query should return only 1000 records (for each employee the latest report for that month). How can i do this without using cursor?

thanks,
Dilip

Join the tables and retrieve the records with the max. date, like this:

select *
from employees e inner join reports r on e.empid=r.empid and r.repdate=(select max(repdate) from reports r2 where r2.empid=r.empid)
 

[dilipananth1984]

Join the tables and retrieve the records with the max. date, like this:

select *
from employees e inner join reports r on e.empid=r.empid and r.repdate=(select max(repdate) from reports r2 where r2.empid=r.empid)
 

sorry i had reported it instead of replying...

thanks a lot for that reply...was very helpful and it only shows that i need to learn the basics . any idea where i can start from?

[azimmer]

sorry i had reported it instead of replying...

thanks a lot for that reply...was very helpful and it only shows that i need to learn the basics . any idea where i can start from?

Not really, if you mean a certain book or URL. I suggest that you practice, look at what others do (look at the threads in this forum for example), check the docs (especially the help and not only what you need at a point but see other topics as well).

Others may be able to point you to some good sources as well.