如何向此 MySQL 查询添加 LEFT JOIN?

发布于 2024-09-07 20:41:02 字数 932 浏览 4 评论 0原文

我在这方面还是个初学者,所以如果这对你们中的一些人来说有点简单,请原谅我。

所以,我在这里有一个疑问:

SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
                    code_tags.*, 
                    tags.*, 
                    users.firstname AS authorname, 
                    users.id AS authorid,
                    GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
                    FROM code, code_tags, tags, users
                    WHERE users.id = code.author AND code_tags.code_id = code.id AND tags.id = code_tags.tag_id
                    GROUP BY code_id
                    ORDER BY date DESC

非常强烈。我想从表评论中计算 code 提交的评论数量。我无法将其添加为 WHERE comments.codeid = code.id 因为这样就不会从数据库中选择没有评论的提交。

如何添加类似于 LEFT JOIN comments.codeid ON code.id = comments.codeid 的内容或类似内容?

谢谢!

杰克

I'm quite a beginner at this so please forgive me if this seems a bit easy for some of you.

So, I have this query here:

SELECT code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
                    code_tags.*, 
                    tags.*, 
                    users.firstname AS authorname, 
                    users.id AS authorid,
                    GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
                    FROM code, code_tags, tags, users
                    WHERE users.id = code.author AND code_tags.code_id = code.id AND tags.id = code_tags.tag_id
                    GROUP BY code_id
                    ORDER BY date DESC

Pretty intense. I want to count the number of comments a code submission has from the table comments. I can't add it as a WHERE comments.codeid = code.id because then that won't select submissions from the database without comments.

How can I add in something along the lines of LEFT JOIN comments.codeid ON code.id = comments.codeid or something along those lines?

Thanks!

Jack

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

鹊巢 2024-09-14 20:41:02

将现有联接更改为正确的 SQL-92 JOINS,并且添加 left join 变得微不足道。

SELECT 
  code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
  code_tags.*, 
  tags.*, 
  users.firstname AS authorname, 
  users.id AS authorid,
  GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
FROM 
  code
  join code_tags on code_tags.code_id = code.id
  join tags on tags.id = code_tags.tag_id
  join users on users.id = code.author
  left join comments on comments.codeid = code.id
GROUP BY code_id
ORDER BY date DESC

请注意,作为一般经验法则,在查询中选择 * 被认为是一个坏主意。仅检索您实际需要的列。

Change your existing joins to be proper SQL-92 JOINS, and adding the left joins becomes trivial.

SELECT 
  code.id AS codeid, code.title AS codetitle, code.summary AS codesummary, code.author AS codeauthor, code.rating AS rating, code.date,
  code_tags.*, 
  tags.*, 
  users.firstname AS authorname, 
  users.id AS authorid,
  GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup
FROM 
  code
  join code_tags on code_tags.code_id = code.id
  join tags on tags.id = code_tags.tag_id
  join users on users.id = code.author
  left join comments on comments.codeid = code.id
GROUP BY code_id
ORDER BY date DESC

Note that as a general rule-of-thumb, selecting * in a query is considered a bad idea. Only retrieve the columns that you actually need.

自此以后,行同陌路 2024-09-14 20:41:02

使用 count(comments.code_id) 和 left join 来获取评论数。
为此,您需要按除 comments.code_id 之外的所有返回列进行分组
某些数据库(例如 MS Access)无法对使用 * 选择的字段进行分组,因此您必须按名称选择它们,然后将它们包含在分组依据中。

例如,如果表code_tags具有字段code_idtag_id,并且表tags具有字段id标签
那么它应该看起来像:

SELECT code.id AS codeid, code.title AS codetitle,  
code.summary AS codesummary, code.author AS codeauthor,  
code.rating AS rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tag,  
users.firstname AS authorname, users.id AS authorid,  
GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup  
count(comments.code_id) as NumComments  
FROM  
code JOIN code_tags ON code_tags.code_id = code.id  
JOIN tags ON tags.id = code_tags.tag_id  
JOIN users ON users.id = code.author  
LEFT JOIN comments ON comments.code_id = code.id  
GROUP BY code_id, codetitle, codesummary, codeauthor, rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tagName,
authorname, authorid, taggroup
ORDER BY date DESC  

Use count(comments.code_id) and left join to get the number of comments.
For that to work, you need to group by all the returned columns except comments.code_id
Some databases (eg MS Access) can't group on fields that are selected with *, so you will have to select them by name and then include them in the group by.

eg if table code_tags has fields code_id and tag_id and table tags has fields id and tag
then it should look like:

SELECT code.id AS codeid, code.title AS codetitle,  
code.summary AS codesummary, code.author AS codeauthor,  
code.rating AS rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tag,  
users.firstname AS authorname, users.id AS authorid,  
GROUP_CONCAT(tags.tag SEPARATOR ', ') AS taggroup  
count(comments.code_id) as NumComments  
FROM  
code JOIN code_tags ON code_tags.code_id = code.id  
JOIN tags ON tags.id = code_tags.tag_id  
JOIN users ON users.id = code.author  
LEFT JOIN comments ON comments.code_id = code.id  
GROUP BY code_id, codetitle, codesummary, codeauthor, rating, code.date,  
code_tags.code_id, code_tags.tag_id, tags.id, tags.tagName,
authorname, authorid, taggroup
ORDER BY date DESC  
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文