Hey guys I have a query and it works fine, but I want to add another table to the mix. The invite table I want to add has two fields: username and user_invite. Much like this site, I am using a point system to encourage diligent users. The current query which is displayed below adds the up votes and down votes based on the user in question: $creator. I want to count the number of entries for that same user from the invite table, and add 50 for each row it finds to the current output/sum of my query. Is this possible with one query, or do I need two?
"SELECT *, SUM(IF(points_id = \"1\", 1,0))-SUM(IF(points_id = \"2\", 1,0)) AS 'total' FROM points LEFT JOIN post ON post.post_id=points.points_id WHERE post.creator='$creator'"
This should work :
SELECT *,**SUM(IF(points_id = "1", 1,0))-SUM(IF(points_id = "2", 1,0))+(select count(*)*50 from inivite where username='$creator') AS 'total'**, FROM points LEFT JOIN post ON post.post_id=points.points_id WHERE post.creator='$creator'"
Assuming that there might be no correspondence in invite table, I used outer join and coalesce:
SET @good='1', @bad='2', @creator='$creator'; SELECT *, SUM(IF(points_id=@good, 1,0))-SUM(IF(points_id=@bad, 1,0))+COALESCE(inv_cnt, 0) * 50) AS total FROM points LEFT JOIN post ON post.post_id=points.points_id LEFT OUTER JOIN (SELECT username, COUNT(user_invite) as inv_cnt FROM invite GROUP BY username) invites ON post.creator = invites.username WHERE post.creator=@creator;
Designing this query with limited knowledge of the schema...
SELECT *, <--
SUM(IF(points_id = \"1\", 1,0))
-SUM(IF(points_id = \"2\", 1,0))
+ 50 * COUNT(invite.user_invite) AS 'total'
FROM points <--
LEFT JOIN post ON post.post_id=points.points_id
LEFT JOIN invite ON post.creator = invite.user_invite
The important thing here is the extra lines, which I've marked with "<--". One is for JOINing your two tables together, the other is to modify the argument of the SUM function.
Post back if this doesn't work.