Question About Inner Joins and COUNT()

[loffing4]

I'm trying to write a query that selects the top five members of a forum based on how many total points they have. A member's total points are calculated as follows: (3 * # answers posted) + (10 * # questions posted) + (15 * # members recruited). The relevant parts of the tables are listed below:

table members (
     member_id int(10),
     ...
     recruiter_id int(10)  /* id of member who recruited this member (0 if none) */
)
 
table answers (
     ...
     member_fk int(10)  /* id of member who posted this answer */
)
 
table questions (
     ...
     member_fk int(10)  /* id of member who posted this question */
)
 

Can this be done in a single query or do I have to total the answer, question, and recruitment points separately? I tinkered around with some inner joins and count()s and got nowhere.

[r035198x]

It should be possible with one query. Post the query you've tried and the relevant parts of the tables. The table structures you posted above are missing the important columns.

[loffing4]

The full table structures are below. Any query I wrote isn't worth posting as I'm not really sure how to get started. I would think I'd need to inner join the tables based on the member id, but I couldn't get it to work. I'm pretty new to mySQL and databases in general.

CREATE TABLE `members` (
  `member_id` int(10) NOT NULL auto_increment,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varbinary(32) NOT NULL,
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `recruiter` int(10) NOT NULL default '0',
  PRIMARY KEY  (`member_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
CREATE TABLE `answers` (
  `answer_id` int(10) NOT NULL auto_increment,
  `member_fk` int(10) default NULL,
  `question_fk` int(10) default NULL,
  `created` datetime default NULL,
  PRIMARY KEY  (`answer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
CREATE TABLE `questions` (
  `question_id` int(10) NOT NULL auto_increment,
  `text` varchar(255) NOT NULL,
  `member_fk` int(10) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `views` int(10) NOT NULL default '0',
  `category_fk` int(10) NOT NULL default '0',
  `enabled` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`question_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 

[loffing4]

Anybody have an idea?

[r035198x]

Anybody have an idea?

So are the total points given by

select 
(3 * (select count(a.answer_id) from answers a where a.member_fk = m.member_ID) 
 
+ (10 * (select count(q.question_id) from questions q where a.member_fk = m.member_ID) 
+ (15 * (select count(m1.recruiter) from members m1 where m1.recruiter = m.member_ID) 
from members m

?

[loffing4]

Perfect! I just had to add a column alias and an order by clause.

The nested select method wasn't one I was familiar with.

[r035198x]

Perfect! I just had to add a column alias and an order by clause.

The nested select method wasn't one I was familiar with.

Glad you managed to finish it off yourself. Good luck with the rest of it.