Results 1 to 11 of 11

Thread: Need help...SQL Query 1

  1. #1

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,633

    Unhappy Need help...SQL Query 1

    I have 2 tables.
    • tblMembers
    • tblMemberCourses

    tblMembers:
    • ID
    • MemberName
    • Age

    tblMemberCourses:
    • ID
    • EntryDate
    • MemberID
    • CourseID

    Some test data would be:
    tblMembers:
    Code:
    ID    MemberName    Age 
    -------------------------------
     1          Woof              4
     5          Moose            79
    tblMemberCourses:
    Code:
    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:
    Code:
    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

  2. #2

  3. #3
    Frenzied Member Lightning's Avatar
    Join Date
    Oct 2002
    Location
    Eygelshoven
    Posts
    1,611
    This should (could) work:
    VB Code:
    1. select M.ID , M.MemberName , MC.CourseID from
    2. FROM tblMembers M
    3. INNER JOIN tblMemberCourses MC on MC.MemberID = M.ID
    4. where MC.date = ( select max(EntryDate ) from tblMemberCourses MC2 where MC2.MemberID = M.ID)
    VB6 & C# (WCF LINQ) mostly


    If you need help with a WPF/WCF question post in the NEW WPF & WCF forum and we will try help the best we can

    My site

    My blog, couding troubles and solutions

    Free online tools

  4. #4

  5. #5
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174

    Re: Need help...SQL Query 1

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

    Code:
    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

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    Id is a bit ambigous for a name - but you have to live with it...

    Code:
    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...

  7. #7
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,174
    Aah ****... too late.

  8. #8

    Thread Starter
    Super Moderator Wokawidget's Avatar
    Join Date
    Nov 2001
    Location
    Headingly Occupation: Classified
    Posts
    9,633
    Originally posted by szlamany
    Id is a bit ambigous for a name - but you have to live with it...

    Code:
    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

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882
    Then...

    Code:
    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?

  10. #10

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,972
    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

    Code:
    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:
    Code:
    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.

    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width