当字段可能为空时，对三个表使用联接 [英] Using a join with three tables when a field might be null

问题描述

下面的代码效果很好.它合并了两个MySQL表中的数据.我想通过从名为comment的第三个MySQL表中提取一些数据来修改它.

The code below works great. It combines data from two MySQL tables. I would like to modify it by pulling in some data from a third MySQL table called comment.

在下面的HTML表中，title是MySQL表submission中的一个字段.每个title都有一个对应的submissionid字段.在comment MySQL表中也可以找到字段submissionid.

In the HTML table below, title is a field in the MySQL table submission. Every title has a corresponding submissionid field. The field submissionid is also found in the comment MySQL table.

在下面的HTML表格中，我希望countComments等于对于任何给定的submissionid，名为commentid的字段出现在MySQL表comment中的次数，其中submissionid是相同的在submission和comment表中，并且submissionid对应于正在使用的title.

In the HTML table below, I would like countComments to equal the number of times a field called commentid appears in the MySQL table comment for any given submissionid,where the submissionid is the same in both the submission and comment tables, and where the submissionid corresponds to the title being used.

这很重要:如果MySQL表comment中没有与submissionid用于table的行相对应的行，我希望countComments等于零.

Here's the catch: if there is no row in the MySQL table comment that corresponds with the submissionid being used for table, I would like countComments to equal to zero.

我该怎么做?

$sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username
             FROM submission AS s,
                  login AS l
            WHERE s.loginid = l.loginid
         ORDER BY s.datesubmitted DESC
            LIMIT 10";


$result = mysql_query($sqlStr);

$arr = array(); 
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) { 
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>';
    echo '</tr>';
    }
echo "</table>";

推荐答案

您正在执行内部联接，但是您需要外部联接，尤其是左侧联接.使用LEFT JOIN时，左侧"表始终会连接在一起，如果没有对应关系，则右侧表的字段将设置为null.在维基百科页面中对此差异进行了很好的解释.

You are doing an INNER JOIN, but you need an OUTER JOIN, in particular, a LEFT JOIN. With a LEFT JOIN, the table "on the left" is always joined, and if there is no correspondence the fields of the table on the right are set to null. The difference is explained very well in this wikipedia page.

然后，您必须将具有相同提交ID的行分组，并计算已分组的行数，请注意，如果一个提交仅包含一条评论而另一条都没有评论，则它们都将1行分组...如果一个提交没有评论，在下一个查询中c.submissionid将为空，因此

Then you have to group the rows with the same submission id and count how much rows have been grouped, taking care that if one submission has only one comment and another have none they both have 1 grouped row... If one submission has no comments, in the next query c.submissionid will be null, so

您的SQL可能是

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, c.submissionid, 
IF(c.submissionid IS NULL, 0, COUNT(*))  AS countComments
FROM submission AS s
INNER JOIN login AS l ON l.loginid = s.loginid
LEFT JOIN comments AS c ON c.submissionid = s.id
GROUP BY (s.id)
ORDER BY s.datesubmitted DESC
LIMIT 10

可能有错误，我没有测试查询...但是我希望给您一个正确的主意，那就是外部联接和内部联接之间的区别.

There can be errors, I have not tested the query... But I hope to have given you the right idea, that is the difference between outer joins and inner joins.

这篇关于当字段可能为空时，对三个表使用联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！