mysql - 连接多个表 - 评论和回复

Question

我尝试创建不同用户的问题和答案的查询。我的目标是将不同的表连接在一起以创建我想要的结果。目前，SQL 查询始终在评论列中生成相同的内容，即来自提出问题的用户。因此，根本不输出用户A的答案。我用LEFT/RIGHT更改了INNER JOIN，然后它可以工作，但问题(评论)就会消失。

SELECT C.username UserQ
     , R.username UserA
     , C.commentID
     , D.Title
     , C.text Comment 
  FROM Comments C 
  JOIN Detail D 
    ON C.vID = D.vId 
  JOIN Search S 
    ON C.vID = S.vID 
  JOIN Replies R 
    ON C.commentID = R.CommentID_ParentID 
 WHERE D.categoryId = 25 
   AND S.searched_keyword = 'stackoverflow' 
 GROUP  
    BY UserQ
     , UserA
     , commentID
     , Title
     , Comment


Current Outcome:

UserQ       UserA     commentID    Title         Comment
Sven        Katja     213          Need Help     Hi, I need help
Sven        Sven      214          Answer1       Hi, I need help
Sven        Ben       215          Answer2       Hi, I need help
Tess        NULL      216          Soccer        I love soccer
Carl        Tanja     216          Question      Do I need SQL for jobs? 
Carl        Peter     216          Answer        Do I need SQL for jobs?

我还尝试了使用 SQL 语句“UNION”的不同方法，但是 UserQ 和 UserA 之间有明显的分离，这意味着当其中一列被填充时，另一列始终为空.

Desired Outcome:

UserQ       UserA     commentID    Title         Comment
Sven        Katja     213          Need Help     Hi, I need help
Sven        Sven      214          Answer1       Try to solve it like..
Sven        Ben       215          Answer2       You can also try...
Tess        NULL      216          Soccer        I love soccer
Carl        Tanja     216          Question      Do I need SQL for jobs? 
Carl        Peter     216          Answer        Yes...

Answer 1

你试过这个吗？

SELECT C.username UserQ,
       R.username UserA,
       C.commentID,
       D.Title,
       C.text Comment
  FROM Comments C 
  JOIN Detail D 
    ON C.vID = D.vId 
  JOIN Search S 
    ON C.vID = S.vID 
LEFT OUTER JOIN Replies R 
    ON C.commentID = R.CommentID_ParentID 
 WHERE D.categoryId = 25 
   AND S.searched_keyword = 'stackoverflow' 
 ORDER
    BY commentID,
       replyID

实际上，为什么不使用一些示例数据创建一个 SQLfiddle？