Amending a join query to add a new value to a query

Question

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

Answer 1

    $sqlStr3 = "SELECT 
        l.loginid, 
        l.username, 
     l.created,
     DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 5 + COALESCE(scs.total, 0) * 10 - COALESCE(nscs.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


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



    GROUP BY l.loginid
    ORDER BY totalScore2 DESC 
    LIMIT 25";