MySQL LIMIT on LEFT JOIN
My request:
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 this is "LIMIT 1" on the third line. This limits all comments to the newest only, so only posts with the most recent comment will be posted as a comment at all.
If I remove the "LIMIT 1" part from there, I would get a line for every comment in the problem, which is not what I want. I only want a new comment for each issue.
Anyway, I'm not sure if my IFNULL part even works, because that's not what I was in before debugging.
So how could I achieve what I wanted?
+2
source to share
3 answers
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
+2
source to share
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
+3
source to share
Edit
Since MySql has no CTE, 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
0
source to share