Join with three tables
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.login开发者_如何学运维id = l.loginid
ORDER BY s.datesubmitted DESC
LIMIT 10";
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.
精彩评论