Need help...SQL Query 1

[Wokawidget]

I have 2 tables.

• tblMembers
• tblMemberCourses

tblMembers:

• ID
• MemberName
• Age

tblMemberCourses:

• ID
• EntryDate
• MemberID
• CourseID

Some test data would be:
tblMembers:

ID    MemberName    Age 
-------------------------------
 1          Woof              4
 5          Moose            79

tblMemberCourses:

ID    EntryDate   MemberID  CourseID 
-----------------------------------------------
 1      31/01/04         1               1
 2      25/02/04         1               2
 3      07/03/04         1               3
 4      17/04/04         1              NULL
 5      25/02/04         5               1
 6      07/03/04         5               2

What I want to do is create an SQL statement that would bring me back a members current course. The current course is the one with the largest/latest date.
The result set I would like would be:

ID   MemberName   CurrentCourseID
----------------------------------------------
 1          Woof                   NULL
 5         Moose                     2

I know it's not that hard, but my brain isn't in SQL mode today

Woof

[Wokawidget]

I am looking for some code a bit like:

SELECT tblMembers.ID, tblMembers.MemberName, [something].CourseID
FROM tblMembers
INNER JOIN [something]

But I seem to be going round in circles

Woof

[Lightning]

This should (could) work:

select M.ID , M.MemberName , MC.CourseID from 
FROM tblMembers M 
INNER JOIN tblMemberCourses MC on MC.MemberID = M.ID
where MC.date = ( select max(EntryDate ) from tblMemberCourses MC2 where MC2.MemberID = M.ID)

[Wokawidget]

That's the one! *bangs head against wall*



Thank you.

Woof

[mendhak]

I didn't read Lightning's response, but here's a rough idea:

SELECT  
M.ID,
M.MemberName,
X.CourseID
FROM
tblMembers M

LEFT JOIN

(
SELECT 
MAX(TMC.EntryDate) AS EntryDate, 
TMC.MemberId
FROM 
tblMemberCourses TMC

GROUP BY 
TMC.Id
) Y

ON

M.MemberId = Y.MemberID

LEFT JOIN

(SELECT
MemberId,
CourseID
FROM
tblMemberCourses
) X
ON
Y.MemberId = X.MemberId

[szlamany]

Id is a bit ambigous for a name - but you have to live with it...

SELECT tblMembers.ID, tblMembers.MemberName, cr.CourseID,max(cr.entrydate)
FROM tblMembers me
INNER JOIN tblMemberCourses cr on cr.memberid=me.id
Group by  tblMembers.ID, tblMembers.MemberName, cr.CourseID

That might work - I'm in a rush to head out to work - so I might have typo...

[mendhak]

Aah ****... too late.

[Wokawidget]

Originally posted by szlamany
Id is a bit ambigous for a name - but you have to live with it...

SELECT tblMembers.ID, tblMembers.MemberName, cr.CourseID,max(cr.entrydate)
FROM tblMembers me
INNER JOIN tblMemberCourses cr on cr.memberid=me.id
Group by  tblMembers.ID, tblMembers.MemberName, cr.CourseID

That might work - I'm in a rush to head out to work - so I might have typo...

that would bring them all back since you have CourseID in the GROUP BY clause and CourseID will be different for each entry.

I don't use ID in my app.
I made the above query up.
I didn't want to release any business info on the web.
My real field and table names are named correctly.

Woof

[szlamany]

Then...

SELECT tblMembers.ID, tblMembers.MemberName, 
,(Select top 1 cr.CourseId from tblMEmberCourses cr where cr.memberid=me.id order by cr.entrydate desc)
FROM tblMembers me

Right?

[Wokawidget]

I'll give that a go.
Lightning already posted a method that works.

Will try out your method too.

Thank you for your help

Fish

[techgnome]

We do something similar.... it requires double joining a table to itself.

Here's the thought process that goes into it.
Select the max date for each member course in tblMemberCourses

SELECT MemberID, MAX(EntryDate) MaxEDate
FROM tblMemberCourses
GROUP BY MemberID

Once you have that, you join it back to tblMemberCourses to get all of the data:

SELECT *
FROM tblMemberCourses MC
INNER JOIN (SELECT MemberID, MAX(EntryDate) MaxEDate
                     FROM tblMemberCourses
                     GROUP BY MemberID) MMC
  ON MC.MemberID = MMC.MemberID
    AND MD.EntryDate = MMC.MaxEDate

That will get you the latest course for each member in the tblMemberCourses table. Now we simply take the tblMembers table and left join it to that.

SELECT *
FROM tblMembers M
LEFT JOIN (SELECT MC.MemberID, MC.EntryDate, MC.CourseID
                  FROM tblMemberCourses MC
                  INNER JOIN (SELECT MemberID, MAX(EntryDate) MaxEDate
                                      FROM tblMemberCourses
                                      GROUP BY MemberID) MMC
                    ON MC.MemberID = MMC.MemberID
                         AND MD.EntryDate = MMC.MaxEDate) MCOURSE
  ON M.MemberID = MCOURSE.MemberID

That will list all in tblMembers, plus the latest course where applicable.

Hope that helps.

TG