Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    31

    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!
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  2. #2
    Join Date
    Dec 2003
    Posts
    31
    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.
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

  3. #3
    Join Date
    Dec 2003
    Posts
    31
    Not one response?
    vBulletin Hacking God - Portfolio
    FireFoxForum.com - Where people talk about FireFox.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •