Ms Sql Server 2000 Query Problem

[AB1234]

Hi

I need to get info from three tables

I need last course client attended for all clients result should show course id and date id and client id in any particular order.



But only want one date and course for each user.

table one

Course ID

table two

Course Code
Course Date
client ID

Table three

Client ID
Client Details

I need this to work in MS SQL SERVER 2000

Any Help MUCH Appreciated

AB

[MAK]

--Table "One" CourseId, CourseName
--Table "Two" CourseId, CourseDate,ClientId
--Table "Three" ClientId, ClientDetails

--Step1 Find Maximum date
Select ClientId,max(CourseDate) as MaxDate from Two group by ClientId

--Step2 Find CourseID for that max date
Select A.CourseId, b.ClientId, b.Maxdate from Two A, (Select ClientId,max(CourseDate) as MaxDate from Two group by ClientId) as b
where A.ClientId=B.ClientId and A.CourseDate =b.MaxDate

--Step3 To display all the info - Final result

Select C.ClientName,D.Coursename,A.CourseId, b.ClientId, b.Maxdate from Two A,
(Select ClientId,max(CourseDate) as MaxDate from Two group by ClientId) as b,
One C, Three D
where A.ClientId=B.ClientId and A.CourseDate =b.MaxDate
and D.ClientId=A.ClientId and C.CourseId=A.CourseId