Problem with query

[AB1234]

Hi

I need to get info from two 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
Table two also has client ID

This is the query that I have so far.

SELECT MAX(DISTINCT dbo.tblCourse.dtmStartDate) AS [Last coursedate], dbo.tblCourse.strCourseCode, dbo.tblClientCourse.lngPrimaryId
FROM dbo.tblCourse INNER JOIN
dbo.tblClientCourse ON dbo.tblCourse.cntCourseId = dbo.tblClientCourse.lngCourseId
GROUP BY dbo.tblCourse.strCourseCode, dbo.tblClientCourse.lngPrimaryId

i am using MS SQL server 2000

Thanks Tried so many things

AB

[jkoopmann]

you should have a client table and then try something like this:

select clienttable.clientid, a.coursecode,a.coursedate
from clienttable, tabletwo a
where clienttable.clientid = a.clientid
and a.coursedate = (select max(b.coursedate) from tabletwo b where b.clientid = a.clientid)

if you don't have a client table, get one, you have a de-normalized model.

if you can't get one, re-post with full table definitions and we will try and help.

[AB1234]

will this work with ms sql server?

thanks

AB

[jkoopmann]

pretty much standard sql.
you could always give it a try

[AB1234]

Hi

the above Query does not work in MS SQL SERVER 2000

any other Ideas?

AB