Solved

MS SQL 2000 Query

Posted on 2009-04-12
3
210 Views
Last Modified: 2012-05-06
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
Comment
Question by:AZZA-KHAMEES
  • 2
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:AZZA-KHAMEES
Comment Utility
Thanks alot. It's working fine
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now