如果没有显示na，则显示最新的帖子注释

Question

我有两个表帖子表和评论表，其中对于一个特定的帖子，可能有评论，或不会有评论。

正在使用以下查询语句：

SELECT id, Post, finaltable.comments FROM table-1 INNER JOIN ( SELECT comments, ticket_id FROM table-2 INNER JOIN 
( SELECT MAX(id) latest_comment_id FROM table-2 GROUP BY ticket_id) latestcomments ON table-2.id = latestcomments.latest_comment_id ) finaltable ON finaltable.ticket_id = table-1.id

这将显示带有最新评论的帖子，但如果没有评论，则不会在all.Only post上显示带有注释的帖子。

Answer 1

您应该使用左联接。

SELECT id, Post,
       CASE WHEN finaltable.comments is null then 'NA' else finaltable.comments end as comments
FROM table-1
LEFT OUTER JOIN (SELECT comments, ticket_id 
                 FROM table-2
                 INNER JOIN (SELECT MAX(id) latest_comment_id
                             FROM table-2  
                             GROUP BY ticket_id) latestcomments
                  ON table-2.id = latestcomments.latest_comment_id ) finaltable
 ON finaltable.ticket_id = table-1.id