Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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
0
AZZA-KHAMEES
Asked:
AZZA-KHAMEES
  • 2
1 Solution
 
Patrick MatthewsCommented:
SELECT e.cpr, q.QualDescr --guessing on the column name here!
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
0
 
Patrick MatthewsCommented:
Sorry, make that


SELECT e.cpr, e.Name, q.QualDescr --guessing on the column name here!
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
0
 
AZZA-KHAMEESAuthor Commented:
Thanks alot. It's working fine
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now