Adding another parameter to a join query

Question

Hello,

I am using some MySQL tables. Their structures are listed below.

"login":

loginid username password email actcode disabled activated created points website location age gender

Each user has a loginid

"submission":

submissionid loginid title slug url displayurl datesubmitted

In the submission table above, the "loginid" is the loginid of the user that submitted the submission.

"comment":

commentid loginid submissionid comment datecommented

In the comment table above, "loginid" is the loginid of the user who made the comment. "submissionid" is the submission on which the comment was made.

The query below ranks the top 25 loginids by (days the loginid has been in existence) + (total submissions by the loginid) * 10 + (comments by the loginid).

I would like to include one more factor: (total comments made on submissionids that the loginid has submitted) * 10.

It would require the following:

1. Getting all submissionids in the table "submission" for a given loginid
2. Summing the total number of entries in the table "comment" that have those submissionids

How can I do this?

Thanks in advance,

John

$sqlStr2 = "SELECT 
    l.loginid, 
    l.username, 
    l.created,
    DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 10 + COALESCE(c.total, 0) AS totalScore2
FROM login l    
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM submission 
    GROUP BY loginid
) s ON l.loginid = s.loginid
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM comment 
    GROUP BY loginid
) c ON l.loginid = c.loginid
GROUP BY l.loginid
ORDER BY totalScore2 DESC 
LIMIT 25";

Answer 1

Introduce the below into your query as an additional join and then include the total * 10 into your totalscore2 calculation.

LEFT JOIN (
    SELECT S2.loginid, COUNT(1) AS total 
    FROM submission S2
    INNER JOIN comment C2
    ON C2.submissionid = S2.submissionid
    GROUP BY S2.loginid
) scs ON scs.loginid = l.loginid