0

I am trying to join two datasets and find the most recent certification date

I the following 2 datasets

Certifications

  • (1, 'Judge Level 1', 'Judge', 10, 2),
  • (2, 'Judge Level 2', 'Judge', 20, 2),
  • (3, 'Judge Level 3', 'judge', 30, 2),
  • (4, 'Olympic Judge', 'olympic judge', 30, 2),
  • (5, 'Junior Leader', 'leadership', 10, 1),
  • (6, 'Trainee', 'leadership', 10, 1),
  • (7, 'Instructor', 'leadership', 10, 1);

members_certifications

  • (1000052, 7, '2016-07-01'),
  • (1000053, 1, '2016-07-01'),
  • (1000053, 3, '2018-07-01'),
  • (1000053, 4, '2019-07-01'),
  • (1000053, 5, '2020-07-01'),
  • (1000059, 1, '2016-07-01'),
  • (1000059, 2, '2017-07-01'),
  • (1000059, 5, '2016-07-04'),
  • (1000059, 6, '2016-07-01'),
  • (1000059, 6, '2016-07-02');

I would like the result set to be

  • 1000059, Junior Leader , leadership, 10, 2016-09-04
  • 1000059, Judge,Judge Level 2, 20, 2016-09-04

I created a exact example http://sqlfiddle.com/#!9/20c30b1/3/1 MySql version 5.6

The first query produces a large data set when I reduce the query by setting a specific id (in this case 100059) I get 0 results.

Also is there a more query efficient or less bulky way create this query.

CREATE TABLE `certifications` (
  `id` int(11) NOT NULL,
  `title` varchar(254) NOT NULL,
  `type` varchar(254) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `expiration_interval` tinyint(4) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `members_certifications` (
  `member_id` int(11) NOT NULL,
  `certification_id` int(11) NOT NULL,
  `effective_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query 1

SELECT members_certifications.member_id AS member_id,certifications.title AS title,
certifications.type AS type,certifications.sort_order AS sort_order,
MaxTable.effective_date AS effective_date FROM members_certifications, certifications,
(SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,
 MAX(members_certifications.effective_date) AS effective_date   
 FROM     members_certifications, certifications  
 WHERE     members_certifications.certification_id = certifications.id   
 GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable 
 WHERE  members_certifications.certification_id = certifications.id
 AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title;

query II (0 results)

SELECT members_certifications.member_id AS member_id,certifications.title AS title,certifications.type AS type,certifications.sort_order AS sort_order,MaxTable.effective_date AS effective_date FROM members_certifications, certifications,  (SELECT     members_certifications.member_id AS member_id,     certifications.type AS type,     MAX(members_certifications.effective_date) AS effective_date   FROM     members_certifications, certifications   WHERE     members_certifications.certification_id = certifications.id   GROUP BY      certifications.type,members_certifications.member_id   ) MaxTable WHERE  members_certifications.certification_id = certifications.id  AND members_certifications.member_id = 100059  AND certifications.type = MaxTable.type  AND members_certifications.member_id = MaxTable.member_id  AND members_certifications.effective_date = MaxTable.effective_date ORDER BY  MaxTable.member_id, certifications.sort_order, MaxTable.type, certifications.title ;

1 Answer 1

0

I'm not exactly sure what you are asking, but I would suggest that you use aliases and ANSI joins for your queries:

SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date   
      FROM members_certifications mc2
         , certifications c2 
      WHERE mc2.certification_id = c2.id   
      GROUP BY c2.type, mc2.member_id   
     ) MaxTable 
    ON c1.type = MaxTable.type  
   AND mc1.member_id = MaxTable.member_id  
   AND mc1.effective_date = MaxTable.effective_date 
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

Regarding your second query in the fiddle, there is a slight difference between:

1000059 and 100059

So if you change that to:

SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date 
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date   
      FROM members_certifications mc2
         , certifications c2 
      WHERE mc2.certification_id = c2.id   
      GROUP BY c2.type, mc2.member_id   
     ) MaxTable 
    ON c1.type = MaxTable.type  
   AND mc1.member_id = MaxTable.member_id  
   AND mc1.effective_date = MaxTable.effective_date 
WHERE mc1.member_id = 1000059   
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

does that give you the expected answer?

MySQL 8+ supports window functions which are far more efficient. Instead of doing a join against a sub-select you can use a window function to determine max_effective_date, and then filter with that:

SELECT member_id, title, type, sort_order, effective_date
FROM (
    SELECT mc1.member_id AS member_id
          ,c1.title AS title
          ,c1.type AS type
          ,c1.sort_order AS sort_order
          ,mc1.effective_date  
          ,Max(effective_date) over (partition by member_id, c2.type) AS max_effective_date 
   FROM members_certifications mc1
   JOIN certifications c1
        ON mc1.certification_id = c1.id
) AS T
WHERE max_effective_date = effective_date 
ORDER BY member_id, sort_order, type, title; 
2
  • Tthe change of the member_id gave the expected data, Will the ansi joins work like LEFT JOINS such that if there is no data null shows up?
    – Ken
    Jul 6, 2020 at 17:30
  • No, join is the same as inner join. If you want an left outer join, use left join or left outer join. Jul 6, 2020 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.