此查询工作正常:

$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";

如果我想将第三个MySQL表(称为“comment”)加入其中,这个方法是否可行?
$sqlStr = "SELECT s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username, count(c.comment) countComments
             FROM submission AS s,
                  login AS l,
                  comment AS c,
            WHERE s.loginid = l.loginid
              AND s.submissionid = c.submissionid
         ORDER BY s.datesubmitted DESC
            LIMIT 10";

提前谢谢你,
约翰

最佳答案

您真的应该使用连接,它更清楚,并且使您的连接条件不在where子句中。另外,当您想在中添加第三个表时,它更加一致:
这是您的原始代码,使用显式连接语法:

SELECT      s.loginid, s.title, s.url, s.displayurl, l.username
FROM        submission s
INNER JOIN  login l ON l.loginid = s.loginid
ORDER BY    s.datesubmitted DESC
LIMIT       10

然后,如果要添加第三个表,很容易:
SELECT      s.loginid, s.title, s.url, s.displayurl, l.username
FROM        submission s
INNER JOIN  login l ON l.loginid = s.loginid
INNER JOIN  comment c ON s.submissionid = c.submissionid
ORDER BY    s.datesubmitted DESC
LIMIT       10

还有,这里有一个技巧。如果连接的两列名称相同,则可以使用USING语法:
INNER JOIN  login l USING (loginid)
INNER JOIN  comment c USING(submissionid)

关于php - 向联接中添加第三个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2494776/

10-11 04:51