>Hi all
>
>I have 2 tables A & B as follows
>
>Table A
> 1. emp_no
> 2. name
>
>Table B
> 1. effective_date
> 2. event_sequence
> 3. eveny_type
> 4. salary
>
>Table B is the career progression of employee.
>One employee can have multiple records in table B and can have multiple records on same date.
>
>I wish to join table A & B in such a way that it returns for each employee, the table B record for a given date with the highest sequence.
>
>Your advice is truly appreciated
>
>Thanks & Best Regards
Assuming you forgot to mention that table B has also Emp_No field, in VFP 9 you can do the following:
select Emp.*, S.Effective_date, S.Event_Sequence, S.Event_Type, S.Salary ;
from Employees Emp inner join EmployeeCareer S on Emp.Emp_No = S.Emp_No ;
INNER JOIN (select Emp_No, max(Event_Sequence) as Event_Sequence from EmployeeCareer GROUP BY Emp_NO) X ;
ON S.Emp_NO = X.Emp_No and S.Event_sequence = X.Event_Sequence
.
See also these blogs (they are for SQL Server, but some ideas can be applied to VFP):
Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog
If it's not broken, fix it until it is.
My Blog