ArizonaJohn Posted October 8, 2010 Share Posted October 8, 2010 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"; Link to comment https://forums.phpfreaks.com/topic/215409-adding-to-a-join-query/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.