Join with three tables

Question

Hello,

For the join query below, I would like to pull some data from a third MySQL table called "comment." Each s.title has a corresponding s.submissionid. The field "submissionid" is also the in the table "comment."

For each "submissionid" in the table "comment," I would like to count a field called "commentid."

How can I do this?

Thanks in advance,

John

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

Answer 1

Yes you can do it but the problem you're going to have is that if a login has multiple submissions and a submission has multiple comments, your results may be hard to interpret.

To put it another way, if you have 10 logins, each has 5 submissions and each submission has 8 comments you're going to pull back 10 x 5 x 8 = 400 rows. Now you can deal with that but the data may get out of hand.

That being:

SELECT s.loginid, s.title, s.url, s.displayurl, l.username, c.commentid
FROM submission s
JOIN login l ON s.loginid = l.loginid
LEFT JOIN comments c ON s.submissionid = c.submissionid
ORDER BY s.datesubmissed DESC
LIMIT 10

Note: I've changed the above to use ANSI SQL JOIN syntax. You should favour this. It's typically much easier to read.