Rens Tillmann Rens Tillmann - 2 months ago 6
MySQL Question

Not returning all rows with LEFT JOINS and HAVING

I am building a simple forum. What I need to display is a list with the latest Topics and near each topic it's latest reply/post. Something like:

Topic name: | Last reply:
Topic name 1 here | 2013-02-01 by username1
Topic name 2 here | 2013-01-01 by username2
etc.


The thing is that Topics and Replies are both in the same table called 'pages'.
This table has a field template which contains of course an ID for the table 'templates'.

So I simply join templates and filter by the Template.name for the record 'Forum topic'

This will return all topics. Because I also needed the latest reply for each topic I decided to do a subquery with a filter on the same template but now for the record 'Forum reply' and ordered by Reply.date DESC and with a LIMIT 1 which returns the latest reply for this topic. Now it is time to show my current query which works fine for Topics that contain at least 1 reply:

/*----some fields to return----*/
SELECT TopicContent.title, ReplyContent.title, Reply.date, Reply.id,
(
/*----subquery to return latest reply id used inside HAVING later on----*/
SELECT Reply.id
FROM pages AS Reply
INNER JOIN templates AS Template ON Reply.template = Template.id
WHERE Reply.parent_id = Topic.id
AND Template.name = 'Forum reply'
ORDER BY Reply.date DESC
LIMIT 1
) AS reply_id
FROM pages AS Topic
INNER JOIN templates AS Template ON Topic.template = Template.id
INNER JOIN page_content AS TopicContent ON Topic.id = TopicContent.page
/*----left join used because topic could have zero replies if new----*/
LEFT JOIN pages AS Reply ON Topic.id = Reply.parent_id
LEFT JOIN page_content AS ReplyContent ON Reply.id = ReplyContent.page
WHERE Template.name = 'Forum topic'
HAVING Reply.id = reply_id
/*--------------------------------------------------------------------------------*/
/*----HAVING | returns not all topics but with correct latest reply----*/
/*----GROUP BY Topic.id | returns topics correctly but incorrect latest reply-----*/


Most important now is that if a new Topic is submitted it does not contain any replies yet.

So when I use HAVING it returns only Topics that also contain at least one reply.
And when using GROUP BY it does not return the latest replies for the topics that contain one or more replies. And for the topics that do not contain replies it returns NULL which is good.

Do I need to change JOINS? Any ideas to resolve this. Thanks a lot!

Answer

I'd move the MaxId subquery to the from clause as the way you're using GROUP BY won't work for what you're trying to do. Here you just join your resultset to a subquery that retrieves your MaxId field.

SELECT
*
FROM 
   pages
   .
   .
   .
   LEFT OUTER JOIN 
   (
   SELECT 
      ReplyId As [MaxId], 
      p.Template
   FROM 
      Pages p INNER JOIN Templates t ON p.Template = t.Id
   WHERE
      TemplateName = 'Forum Reply'
   ORDER BY ReplyDate LIMIT 1
   ) a ON a.Template = pages.Template
WHERE 
   Template.name =  'Forum topic'

Alternatively (this one could give you more than one reply if multiple first replies have the exact same time in which case you could limit the results)

SELECT
   *
FROM
   pages
   .
   .
   .
   (SELECT
      ReplyId As [MaxiId],
      Template
   FROM
      Pages p INNER JOIN
      Templates t ON p.Template = t.id LEFT OUTER JOIN
      (SELECT 
         p.Template, 
         MIN(ReplyDate) AS [FirstReplyDate]
      FROM      
         Pages p INNER JOIN
         Templates t ON p.Template = t.id
      WHERE
         TemplateName = 'Forum Reply'
      GROUP BY
         p.Template) b ON b.Template = t.id
   WHERE 
      p.ReplyDate = b.FirstReplyDate
   ) a ON a.Template = pages.Template

Either way you should be moving that first reply subquery to the From clause