Can anyone help me with a complex sum, 3 table join mysql query?

Hey guys I have a query and it works fine, but I want to add anoth开发者_开发问答er 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
WHERE post.creator='$creator'

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.