MySQL LIMIT on a LEFT JOIN

Question

My query:

   SELECT issues.*, 
          comments.author AS commentauthor, 
          comments.when_posted AS commentposted
     FROM issues
LEFT JOIN (SELECT * 
             FROM comments 
         ORDER BY when_posted DESC 
            LIMIT 1) AS comments ON issues.id=comments.issue
ORDER BY IFNULL(commentposted, issues.when_opened) DESC

My problem with it is the "LIMIT 1" on the third line. That limits all comments to only the newest one, so only issues with the newest comment will be reported back as having a comment at all.

If I removed the "LIMIT 1" part from there, I'd get a row for every comment in an issue, and that's not what I want. What I want is only the newest comment for each issue.

In any case, I'm not sure if my IFNULL part even works because that's not where I'm up to in debugging yet.

So how would I achieve what I wanted?

Answer 1

   SELECT issues.*, 
          comments.author AS commentauthor, 
          comments.when_posted AS commentposted
     FROM issues
LEFT JOIN ( SELECT c1.issue, c1.author, c1.when_posted
              FROM comments c1
           JOIN
           (SELECT c2.issue, max(c2.when_posted) AS max_when_posted           
              FROM comments c2
          GROUP BY issue) c3
            on c1.issue = c3.issue and c1.when_posted = c3.max_when_posted
          ) AS comments ON issues.id=comments.issue
 ORDER BY COALESCE(commentposted, issues.when_opened) DESC

Answer 2

Try:

   SELECT i.*,
          c.author AS commentauthor,
          c.when_posted AS commentposted
     FROM ISSUES i
LEFT JOIN COMMENTS c ON c.issue = i.id
     JOIN (SELECT c.issue,
                  MAX(c.when_posted) 'max_when_posted'
             FROM COMMENTS c
         GROUP BY c.issue) mwp ON mwp.issue = c.issue
                              AND mwp.max_when_posted = c.when_posted
 ORDER BY COALESCE(c.when_posted, i.when_opened) DESC

Answer 3

Edit

Since MySql does not have CTE's after all, try this:

SELECT i.*
    c.author AS CommentAuthor,
    c.when_posted AS CommentPosted
FROM Issues i
LEFT JOIN 
    (SELECT issue, MAX(when_posted) AS LastPostDate 
     FROM comments GROUP BY issue) ic ON ic.issue = i.id
LEFT JOIN Comment c ON c.issue = i.id AND c.when_posted = ic.LastPostDate  
ORDER BY COALESCE(ic.LastPostDate, issues.when_opened) DESC