连接多个表-注解和回复

mqkwyuun 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(347)

我尝试创建一个不同用户的问题和答案的查询。我的目标是将不同的表连接在一起以创建我想要的结果。目前，sql查询总是在comment列中生成相同的内容，即从提出问题的用户生成的内容。因此，usera的答案根本不输出。我用left/right更改了内部连接，然后它就可以工作了，但是问题（注解）就消失了。

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

sql mysql Database

来源：https://stackoverflow.com/questions/51628274/join-multiple-tables-comment-and-replies

2条答案

按热度按时间

k4emjkb11#

你试过这个吗？

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来处理一些示例数据呢？

赞(0）回复(0）举报 2021-06-20

eulz3vhy2#

内连接、外连接、全连接、左连接
http://www.phpknowhow.com/mysql/joins/

赞(0）回复(0）举报 2021-06-20