Adding to a join query

[ArizonaJohn]

Hello,

 

The query below works well. It pulls information from 3 MySQL tables: login, submission, and comment.

 

It creates a value called totalScore2 based on calculation of values pulled from these three tables.

 

The MySQL tables "comment" and "submission" both have the following fields:

 

loginid submissionid

 

In the table "submission," each "submissionid" has only one entry/row, and thus only one "loginid" associated with it.

 

In the table "comment," the field "submissionid" could have several entries/rows, and could be associated with multiple "loginid"s.

 

Each time one of the "submissionid"s in "comment" is associated with the same "loginid" that it has in the table "submission," I would like to add this as a factor to the equation below. I would like to multiple instances like this times (-10).

 

How could I do this?

 

Thanks in advance,

 

John

 

$sqlStr2 = "SELECT 
    l.loginid, 
    l.username, 
    l.created,
    DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 5 + COALESCE(scs.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
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
GROUP BY l.loginid
ORDER BY totalScore2 DESC 
LIMIT 25";