MS SQL 2000 Query
I have two tables with one to many relationship namley employees (Emp) and qualifications (Qual). I want to select all employees with the most recent qualification. Each employee might have more than one qualification. The two tables are joined by the emp_cpr column. I tried the following SQL query :
SELECT Emp.CPR, Emp.name, Max(Qual.Date) AS Date
FROM Emp INNER JOIN Qual ON emp.cpr = Qual.cpr
GROUP BY Emp.cpr,emp.name
It's working only if I select two columns from the first table namely emp_cpr and emp_name. I want to group all employees by cpr showing only thier latest qualification.
Thanks
SELECT Emp.CPR, Emp.name, Max(Qual.Date) AS Date
FROM Emp INNER JOIN Qual ON emp.cpr = Qual.cpr
GROUP BY Emp.cpr,emp.name
It's working only if I select two columns from the first table namely emp_cpr and emp_name. I want to group all employees by cpr showing only thier latest qualification.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks alot. It's working fine
FROM Emp e INNER JOIN
Qual q ON e.cpr = q.CPR INNER JOIN
(SELECT q2.cpr, Max(q2.Date) AS MaxDate
FROM Qual q2
GROUP BY q2.cpr) z ON q.cpr = z.cpr AND q.Date = z.MaxDate