Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Unanswered: Display only most recent records & expanding on JOIN functions

    I currently the following code in our organization to list all trainees listed to accounts, and the meetings they have held and the session number it was.

    Code:
    SELECT accounts.name AS Account, accounts.account_type AS Type, trainee.name AS Trainee, trainee.category AS Category, meetings.date_start AS Held, meetings_cstm.session_number_c AS Session
    FROM (((((trainee_accounts_1_c
    LEFT JOIN accounts
    ON trainee_accounts_1_c.trainee_accounts_1accounts_idb = accounts.id)
    LEFT JOIN trainee
    ON trainee_accounts_1_c.trainee_accounts_1trainee_ida = trainee.id)
    LEFT JOIN meetings_trainee
    ON trainee.id = meetings_trainee.trainee_id)
    LEFT JOIN meetings
    ON meetings_trainee.meeting_id = meetings.id)
    LEFT JOIN meetings_cstm
    ON meetings.id = meetings_cstm.id_c
    AND meetings.date_start)
    WHERE accounts.account_type IN ('Prospect', 'Customer')
    AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
    AND meetings.status = 'held'
    AND meetings.deleted = 0
    ORDER BY accounts.name, trainee.name, meetings.date_start ASC
    Attached is a sample output from the above code. (Values in Yellow are not generated from the above I will discuss them in a minute) What I need to do is firstly I would like to display only the latest meeting held rather than all meetings held. IE Trainee John Smith would only show meeting Line 4 (not line 3 & 2) as it is the most recent meeting.

    Secondly there are a bunch of trainees that do not have any meetings assigned to them but I still wanted them listed - such as the rows in yellow. I cant seem to get it to work, I've tried experimenting with different JOIN functions however this is all pointless with the below coding as it removes the lines

    Code:
    AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
    AND meetings.status = 'held'
    AND meetings.deleted = 0
    Hope that all makes sense, any help would be greatly appreciated
    Attached Thumbnails Attached Thumbnails Query Output.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT accounts.name AS Account
         , accounts.account_type AS Type
         , trainee.name AS Trainee
         , trainee.category AS Category
         , meetings.date_start AS Held
         , meetings_cstm.session_number_c AS Session
      FROM trainee_accounts_1_c
    LEFT 
      JOIN accounts
        ON accounts.id = trainee_accounts_1_c.trainee_accounts_1accounts_idb
       AND accounts.account_type IN ('Prospect', 'Customer')
    LEFT 
      JOIN trainee
        ON trainee.id = trainee_accounts_1_c.trainee_accounts_1trainee_ida
    LEFT 
      JOIN meetings_trainee
        ON meetings_trainee.trainee_id = trainee.id
    LEFT 
      JOIN meetings
        ON meetings.id = meetings_trainee.meeting_id
       AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
       AND meetings.status = 'held'
       AND meetings.deleted = 0
    LEFT 
      JOIN meetings_cstm
        ON meetings_cstm.id_c = meetings.id
    ORDER 
        BY accounts.name
         , trainee.name
         , meetings.date_start ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2012
    Posts
    5
    Hi r937,

    Thanks for such a quick reply, that works perfectly. The only thing is it is still still listing all meetings assigned to the trainee. Is it possible to only display the latest meeting information? So each trainee will only appear once in the extraction?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    LEFT 
      JOIN meetings
        ON meetings.id = meetings_trainee.meeting_id
       AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
       AND meetings.status = 'held'
       AND meetings.deleted = 0
    to this --
    Code:
    LEFT 
      JOIN ( SELECT meetings_trainee.trainee_id
                  , MAX(date_start) AS latest
               FROM meetings_trainee
             INNER
               JOIN meetings
                 ON meetings.id = meetings_trainee.meeting_id
                AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
                AND meetings.status = 'held'
                AND meetings.deleted = 0
             GROUP
                 BY meetings_trainee.trainee_id ) AS last_meeting 
        ON last_meeting.trainee_id = trainee.id 
    LEFT
      JOIN meetings
        ON meetings.id = meetings_trainee.meeting_id
       AND meetings.date_start = last_meeting.latest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2012
    Posts
    5
    Basically there, however trainee still appears as many times as they have had meetings, the latest displays date, whereas all the others say NULL

    IE
    John Smith NULL NULL
    John Smith NULL NULL
    John Smith 20/07/2012 04:30:00

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about to remove outer join?
    Code:
    ...
    LEFT 
      JOIN meetings_trainee
        ON meetings_trainee.trainee_id = trainee.id
    ...

  7. #7
    Join Date
    Jul 2012
    Posts
    5
    Comes back with #1054 - Unknown column 'meetings_trainee.meeting_id' in 'on clause'

    Was the following right, just incase I missed a step

    Code:
    SELECT accounts.name AS Account
         , accounts.account_type AS Type
         , trainee.name AS Trainee
         , trainee.category AS Category
         , meetings.date_start AS Held
         , meetings_cstm.session_number_c AS Session
      FROM trainee_accounts_1_c
    LEFT 
      JOIN accounts
        ON accounts.id = trainee_accounts_1_c.trainee_accounts_1accounts_idb
       AND accounts.account_type IN ('Prospect', 'Customer')
    LEFT 
      JOIN trainee
        ON trainee.id = trainee_accounts_1_c.trainee_accounts_1trainee_ida
        AND trainee.category IN ('EOI', 'Sign Up', 'Enrolment DoM', 'Active PG', 'Active NSB', 'Active SB', 'Active NSB EW', 'Active Dip Man', 'EOI Dead', 'EOI Ineligible', 'EOI School Dead', 'Completed Sessions', 'Completed Assessment')  
    LEFT 
      JOIN ( SELECT meetings_trainee.trainee_id
                  , MAX(date_start) AS latest
               FROM meetings_trainee
             INNER
               JOIN meetings
                 ON meetings.id = meetings_trainee.meeting_id
                AND meetings.date_start BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY) AND CURDATE()
                AND meetings.status = 'held'
                AND meetings.deleted = 0
             GROUP
                 BY meetings_trainee.trainee_id ) AS last_meeting 
        ON last_meeting.trainee_id = trainee.id 
    LEFT
      JOIN meetings
        ON meetings.id = meetings_trainee.meeting_id
       AND meetings.date_start = last_meeting.latest
    LEFT 
      JOIN meetings_cstm
        ON meetings_cstm.id_c = meetings.id
    ORDER 
        BY accounts.name
         , trainee.name
         , meetings.date_start ASC

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought you might not be able to use meetings_trainee.meeting_id in
    Code:
    ...
    LEFT
      JOIN meetings
        ON meetings.id = meetings_trainee.meeting_id
       AND meetings.date_start = last_meeting.latest
    ...
    If that's the case, try...
    Corrected: Add me .date_start in subquery and replace "me." in out of the subquery to "lm."
    Code:
    SELECT ac.name              AS Account
         , ac.account_type      AS Type
         , tr.name              AS Trainee
         , tr.category          AS Category
         , lm.date_start        AS Held
         , mc.session_number_c  AS Session
     FROM
           trainee_accounts_1_c AS ta
     LEFT  JOIN
           accounts             AS ac
      ON   ac.id = ta.trainee_accounts_1accounts_idb
       AND ac.account_type IN ('Prospect', 'Customer')
     LEFT  JOIN
           trainee              AS tr
      ON   tr.id = ta.trainee_accounts_1trainee_ida
       AND tr.category IN ( 'EOI'       , 'Sign Up'        , 'Enrolment DoM'
                          , 'Active PG' , 'Active NSB'
                          , 'Active SB' , 'Active NSB EW'  , 'Active Dip Man'
                          , 'EOI Dead'  , 'EOI Ineligible' , 'EOI School Dead'
                          , 'Completed Sessions'
                          , 'Completed Assessment'
                          )
     LEFT  JOIN
          (SELECT m_t.trainee_id
                , me .date_start
                , me .id
                , ROW_NUMBER
                     OVER( PARTITION BY m_t.trainee_id
                               ORDER BY me .date_start DESC
                         ) AS rnum
            FROM  meetings_trainee AS m_t
            INNER JOIN
                  meetings         AS me
             ON   me.id = m_t.meeting_id
            WHERE me.date_start
                  BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY)
                      AND CURDATE()
              AND me.status = 'held'
              AND me.deleted = 0
          ) AS lm /* last_meeting */
      ON   lm.trainee_id = tr.id
       AND lm.rnum       = 1
     LEFT  JOIN
           meetings_cstm        AS mc
      ON   mc.id_c = lm.id
     ORDER BY
           ac.name
         , tr.name
         , lm.date_start ASC
    ;
    Last edited by tonkuma; 07-21-12 at 11:27. Reason: Add me .date_start in subquery and replace "me." in out of the subquery to "lm."

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ROW_NUMBER OVER... would be nice, but this is mysql

    perhaps this may be of interest? ... http://www.dbforums.com/mysql/166832...ion-mysql.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes!
    I also realized that after posted.

    So, I thoght another query.
    Code:
    SELECT ac.name              AS Account
         , ac.account_type      AS Type
         , tr.name              AS Trainee
         , tr.category          AS Category
         , me.date_start        AS Held
         , mc.session_number_c  AS Session
     FROM
           trainee_accounts_1_c AS ta
     LEFT  JOIN
           accounts             AS ac
      ON   ac.id = ta.trainee_accounts_1accounts_idb
       AND ac.account_type IN ('Prospect', 'Customer')
     LEFT  JOIN
           trainee              AS tr
      ON   tr.id = ta.trainee_accounts_1trainee_ida
       AND tr.category IN ( 'EOI'       , 'Sign Up'        , 'Enrolment DoM'
                          , 'Active PG' , 'Active NSB'
                          , 'Active SB' , 'Active NSB EW'  , 'Active Dip Man'
                          , 'EOI Dead'  , 'EOI Ineligible' , 'EOI School Dead'
                          , 'Completed Sessions'
                          , 'Completed Assessment'
                          )
     LEFT  JOIN
    /* ( */
          (SELECT m_t.trainee_id
                , MAX(me.date_start) AS date_start
            FROM  meetings_trainee AS m_t
            INNER JOIN
                  meetings         AS me
             ON   me.id = m_t.meeting_id
            WHERE me.date_start
                  BETWEEN DATE_ADD(CURDATE(), INTERVAL -60 DAY)
                      AND CURDATE()
              AND me.status = 'held'
              AND me.deleted = 0
            GROUP BY
                  m_t.trainee_id
          ) lm /* last_meeting */
     INNER JOIN
           meetings_trainee     AS m_t
      ON   m_t.trainee_id = lm.trainee_id
     INNER JOIN
           meetings             AS me
      ON   me.id         = m_t.meeting_id
       AND me.date_start = lm.date_start
    /* ) */
      ON   lm.trainee_id = tr.id
     LEFT  JOIN
           meetings_cstm        AS mc
      ON   mc.id_c = me.id
     ORDER BY
           ac.name
         , tr.name
         , me.date_start ASC
    ;

  11. #11
    Join Date
    Jul 2012
    Posts
    5
    tonkuma thanks so much that works perfectly for me! Far an above my abilities so you have saved me a lot of work!

    r937 that looks very interesting thanks for the link I am playing around with it now

    Thanks guys!

Posting Permissions

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