Join Query Help

Question

Hello,

The query below works well. It pulls data from two MySQL tables, "submission" and "login." I would like to also pull data from a third table called "comment" in the same database.

The table "comment" has the following fields:

commentid, loginid, submissionid, comment, datecommented

Two of the fields in the table "login" are called "loginid" and "username."

In the query below, I would like to count all "commentid" in "comment" where "loginid" equals the "loginid" in "login" where "username" equals "$profile."

How can I do this?

Thanks in advance,

John

$sqlStr1 = "SELECT l.username, l.loginid, s.loginid, s.submissionid, s.title, s.url, s.datesubmitted, s.displayurl, l.created, count(s.submissionid) countSubmissions
               FROM submission AS s
         INNER JOIN login AS l
                 ON s.loginid = l.loginid
              WHERE l.username = '$profile'";

Answer 1

I would use a seperate query, it will probably end up executing quicker that 1 complicated query.

Answer 2

SELECT l.username, l.loginid, s.loginid, 
    s.submissionid, s.title, s.url, s.datesubmitted, s.displayurl, l.created, count(s.submissionid) AS countSubmissions
FROM submission AS s LEFT JOIN login AS l USING(loginid)
WHERE l.username = '$profile'
GROUP BY loginid;