mysql subquery limit is also limiting parent query

Question

SELECT * FROM forum_posts AS post 

INNER JOIN( SELECT parent AS rparent, author AS rauthor, MAX(created_at( AS rdate FROM forum_replies) AS reply
ON post.id = reply.rparent

I want to retrieve all records from forum with 1 latest reply on that thread. Problem is the limit also effects the parent query resulting in only 1 thread being returned.

Help will be greatly appreciated, thanks in advance.

Post the show create table and example data on sqlfriddle and post the link here.. — Raymond Nijland
– Raymond Nijland, Commented Oct 13, 2013 at 12:29
Did you copy this code, or retype it? You have a typo - MAX should have a left parenthesis at the end. And should there be a GROUP BY clause for that MAX? — AgRizzo
– AgRizzo, Commented Oct 13, 2013 at 12:31
If the problem here is that posts with no replies are undesirably excluded from your resultset, you should change from an INNER JOIN to a LEFT JOIN. See A Visual Explanation of SQL Joins for more information. — eggyal
– eggyal, Commented Oct 13, 2013 at 12:53
Fantastic eggyal that worked perfectly! post it as a solution and ill mark it as solved. — TheNickyYo
– TheNickyYo, Commented Oct 13, 2013 at 12:56

Community · Accepted Answer · 2017-05-23 12:28:04Z

As @AgRizzo commented above, you probably need to group the subquery.
Furthermore, an inner join will only result in a record where the join criterion is matched in both tables: that is, posts for which there are no replies will be excluded.

If you wish to keep records from one table even where the join criterion is not matched, you will need an outer join; in this case, a left outer join (so that records from the left operand of the join are always included in the resultset).

See A Visual Explanation of SQL Joins for more information.

Therefore:

SELECT * FROM forum_posts AS post LEFT JOIN (
  SELECT   parent          AS rparent
    ,      author          AS rauthor
    ,      MAX(created_at) AS rdate
  FROM     forum_replies
  GROUP BY parent
) AS reply ON post.id = reply.rparent

Alternatively, perform the grouping after the join:

SELECT      post.*
  ,         reply.parent          AS rparent
  ,         reply.author          AS rauthor
  ,         MAX(reply.created_at) AS rdate
FROM        forum_posts           AS post
  LEFT JOIN forum_replies         AS reply ON reply.parent = post.id
GROUP BY    post.id

Collectives™ on Stack Overflow

mysql subquery limit is also limiting parent query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related