CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 4944

Adding a third table to a Join

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

Upvotes: 2

Views: 146

Answers (3)

bradaric
bradaric

Reputation: 11

You would have to add a GROUP BY clause with one or more fields in it (I've set just the loginid in the example below) as well as you're using the COUNT() function in there, so the SQL would go something like this:

$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
         GROUP BY s.loginid
         ORDER BY s.datesubmitted DESC
            LIMIT 10";

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50990

Almost.

Your syntax is for the third table is correct (although old-style; it uses implicit JOINs).

But to make the COUNT(c.comment) work you must (or I should say "ought to", since MySQL is loose on certain syntax) add a GROUP BY clause to the selection. That clause should name every other column in the SELECT:

SELECT s.loginid, s.submissionid s.title, s.url, 
       s.displayurl, l.username, count(c.comment) AS countComments
FROM submission AS s,
     login AS l, 
     comment AS c,
WHERE s.loginid = l.loginid AND s.submissionid = c.submissionid
GROUP BY s.loginid, s.submissionid s.title, s.url, s.displayurl, l.username
ORDER BY s.datesubmitted DESC
LIMIT 10

Upvotes: 1

ryeguy
ryeguy

Reputation: 66851

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)

Upvotes: 3

Related Questions