Query finds no values when sharppend
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;