CODEWITHSUNDEEP
mysql
TheNickyYo
TheNickyYo

Reputation: 2389

mysql subquery limit is also limiting parent query

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.

Upvotes: 0

Views: 99

Answers (1)

eggyal
eggyal

Reputation: 125865

  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

Upvotes: 1

Related Questions