asked    Nelly     2018-10-22       mysql       124 view        2 Answers

[SOLVED] MYSQL LEFT JOINing the latest record from another table

I have a training_stats table (current due training) and I also have a completed_training table.

What I want to do is query due training with the last completed date from the completed table.

I've nearly got what I want, I get the due training, but they are duplicated with each completed record(as there are many completed records to each current due), and I only want single rows and the latest completed date.

I've been trying to use MAX, and when I run the MAX query independently, I get the last record. But when the MAX query is in the join, it is returning all completed rows.

This is the query that I am using:

SELECT s.course_stat_id
      ,o.org_name
      ,u.id
      ,u.first_name
      ,u.last_name
      ,a.area_id
      ,a.area_name
      ,tc.course_id
      ,tc.course_name
      ,s.assigned_on
      ,s.due
      ,s.pass_mark
      ,s.completed_on
      ,completed.complete_training_id
      ,completed.complete_date
FROM training_stats s
JOIN organisations o ON o.org_id = s.org_id
LEFT JOIN (
    SELECT complete_training_id
          ,user_id
          ,area_id
          ,course_id
          ,max(completed_on) AS complete_date
    FROM completed_training
    GROUP BY complete_training_id
) completed ON completed.user_id = s.user_id
AND completed.area_id = s.area_id
AND completed.course_id = s.course_id
LEFT JOIN users u ON u.id = s.user_id
LEFT JOIN areas a ON a.area_id = s.area_id
LEFT JOIN training_courses tc ON tc.course_id = s.course_id
WHERE u.active = 1
AND o.active = 1
AND s.assigned = 1

Can you see what I am doing wrong?

  2 Answers  

        answered    Belle     2018-10-22      

Not exactly positive of your expected results, but the failure is PROBABLY for your group by and JOIN. Your group by is ONLY on the training ID, but you are also pulling user, area and course as well as max date completed for said respective training ID, user, area, course. You group by and join should match the unique characteristics.

Without seeing data, the query as I interpret it is that the "complete_training_id" is an auto-increment column for that table. Having said that, there would only ever be one record for that ID.

Having said that, the completed training table can have for a single user, area and course, multiple training days of which you want the most recent. For example someone attending college and needs to take many computer classes and they are refreshers from prior so assume all are same course ID. A person could take in 2012, 2014, 2016. You would want the instance of the user/area/course showing the 2016 dated training. So lets look at that first.

select
      ct.user_id,
      ct.area_id,
      ct.course_id,
      max(ct.completed_on) AS complete_date
   FROM 
      completed_training ct
   GROUP BY 
      ct.user_id,
      ct.area_id,
      ct.course_id

Now, for each user, area and course of study, I have one record with the most recent completion date. NOW lets pull the rest of the details, but since you need the completed training ID too, I applied the MAX() of that in the query below. The ID should by default be increasing every time a new record is added, so one completed a year ago would have a lower value than the ID completed today. So you get both the completed ID and its corresponding date for a given user, area, course.

SELECT
      s.course_stat_id,
      o.org_name,
      u.id,
      u.first_name,
      u.last_name,
      a.area_id,
      a.area_name,
      tc.course_id,
      tc.course_name,
      s.assigned_on,
      s.due,
      s.pass_mark,
      s.completed_on,
      ct.complete_training_id,
      ct.complete_date
   FROM 
      training_stats s
         JOIN organisations o 
            ON s.org_id = o.org_id 
           AND o.active = 1
         LEFT JOIN 
            ( select
                    ct.user_id,
                    ct.area_id,
                    ct.course_id,
                    max(ct.complete_training_id ) as complete_training_id,
                    max(ct.completed_on) AS complete_date
                 FROM 
                    completed_training ct
                 GROUP BY 
                    ct.user_id,
                    ct.area_id,
                    ct.course_id ) ct
            on s.user_id = ct.user_id
            AND s.area_id = ct.area_id
            AND s.course_id = ct.course_id
         JOIN users u 
            ON s.user_id = u.id
            AND u.active = 1
         LEFT JOIN areas a 
            ON s.area_id = a.area_id
         LEFT JOIN training_courses tc 
            ON s.course_id = tc.course_id
   WHERE
      s.assigned = 1


        answered    Cathy     2018-10-22      

I'm not 100% sure of that. First, run this query. It should list all completed training, with a rnk from 1 (lastest), to n (oldest).

    SELECT complete_training_id
          ,user_id
          ,area_id
          ,course_id
          ,completed_on AS complete_date
          ,@curRank := case when complete_training_id <> @cur_complete_training_id then 0 else @curRank + 1 end rnk
    FROM completed_training, (select @curRank := 0, @cur_complete_training_id := 0)
    ORDER BY complete_training_id, completed_on DESC

If true, the answer is :

SELECT s.course_stat_id
      ,o.org_name
      ,u.id
      ,u.first_name
      ,u.last_name
      ,a.area_id
      ,a.area_name
      ,tc.course_id
      ,tc.course_name
      ,s.assigned_on
      ,s.due
      ,s.pass_mark
      ,s.completed_on
      ,completed.complete_training_id
      ,completed.complete_date
FROM training_stats s
JOIN organisations o ON o.org_id = s.org_id
LEFT JOIN (
    SELECT complete_training_id
          ,user_id
          ,area_id
          ,course_id
          ,completed_on AS complete_date
          ,@curRank := case when complete_training_id <> @cur_complete_training_id then 0 else @curRank + 1 end rnk
    FROM completed_training, (select @curRank := 0, @cur_complete_training_id := 0)
    ORDER BY complete_training_id, completed_on DESC
) completed ON completed.user_id = s.user_id and completed.rnk = 1
AND completed.area_id = s.area_id
AND completed.course_id = s.course_id
LEFT JOIN users u ON u.id = s.user_id
LEFT JOIN areas a ON a.area_id = s.area_id
LEFT JOIN training_courses tc ON tc.course_id = s.course_id
WHERE u.active = 1
AND o.active = 1
AND s.assigned = 1




Your Answer





 2018-10-22         Kyle

How to rewrite implicit joins to explicit joins?

I'm having some trouble re-writing these JOINS using explicit joins. I'm trying to understand how this is accomplished. Here is the original query using implicit joins that I've inherited:FROM prod.f31122 t, prod.f4801 wo, prod.f30008 r, prod.f0101 abWHERE t.wtdoco = wo.wadoco(+) and t.wtmmcu = wo.wammcu(+) and t.wtmmcu = r.iwmcu and t.wtan8 = ab.aban8I've tried the following, but I'm getting different result sets. The same count, but the result set is incorrect (likely an issue with my groupings which aren't displayed). Here is my translati...
 sql                     3 answers                     111 view
 2018-10-22         Perry

Two databases with same tables and column names, How to pull out one column with similar data while all the other columns showing the differences?

I have 2 databases named 'OK' and 'TX' with same table named 'E_Model' and columns like: 'Product_ID' , 'Description', 'Warranty' and 'Category'I want to have the list of Product ID's which both the tables have and want to know the difference in all the other column at the same time. Desired result should be like:**'*Database' 'Product_ID' , 'Description', 'Warranty' 'Category'***OK LB9456 COFFEE 436 NULL 10 TX LB9456 TOASTER 956 1 12OK QR3300 APPLE 31 3 15TX ...
 sql-server                     1 answers                     105 view
 2018-10-22         Lambert

Text Split cognitive skill not visible in UI

I am adding Azure Search and trying to add skills for content enrichment.I can see the Key Phrase Extraction and the Language Detection predefined skills but not the Text Split skill on the screen. Is there a reason why Text Split skill is not visible? Or is it something that can only be added via API? The capabilities exposed throught the portal focus on core scenarios that customers want to perform so they do not include text splitting. If you want to split your text, you should do it by creating your own skillset programatically through the API, that will allow you t...
 azure                     1 answers                     109 view