var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: selecting several counts from two tables ( was "Need help with query...")
OK, I'm terrible when it comes to GROUP and JOINS... So I'm asking for help. Here is what I need to do. I need to select from 2 tables. I'm going to do my best to explain what I need to do:
I have a user table. This table keeps track of posts made by a user and what user refered them by their userid. So here is what I need to do:
(posts * 3) + (COUNT(refererid) * 5) WHERE userid = refererid
So this will get the number of posts in the user table and the total number of people that user has refered, times 3 and 5 respectively.
With this same query I need to select from the thread table and COUNT(userid) * 4. So I need to count the number of times their userid appears in the thread table.
Now to top all this off I need to count the number of posts and threads that the users they have refered have made and multiply those by 1 and 2 respectively.
So to try to simplify my explaintion here is a simple select that I know won't work but may help you get the idea:
SELECT user.posts * 3 + COUNT(refererid) * 5 + COUNT(thread.userid) * 4 + referer.posts * 2 + COUNT(referer.userid) FROM user, thread WHERE userid=refererid
This makes no sense probably but I'm only using 2 tables but I need to get a bunch of information from 2 different users.
Thanks a ton in advance!
I'm so close to getting this work... This is what I have so far:
SELECT posts, userid, username, COUNT(postuserid) as threads, COUNT(referrerid) as referrer FROM user
LEFT OUTER JOIN thread ON postuserid = userid
GROUP BY userid
But the referer isn't returning the correct value. All I need is to get the number of times thier userid is in the referrerid column.