w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
mysql subquery limit is also limiting parent query
  1. As @AgRizzo commented above, you probably need to group the subquery.

  2. 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




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.