将第三个表添加到Join

Question

This query works fine:

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

Would this work if I wanted to join a third MySQL table (called "comment") to it?

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

Thanks in advance,

John

Answer 1

You should really be using joins, it's more clear and it keeps your join conditions out of your where clause. Also, when you want to add a 3rd table in, it's more consistent:

Here's your original code, using explicit join syntax:

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

Then, if you want to add a third table, it's easy:

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

Also, here's a trick. If you the two columns you join on are the same name, you can use the USING syntax:

INNER JOIN  login l USING (loginid)
INNER JOIN  comment c USING(submissionid)