mysql - Counting the same column on the same table under different alias? -

i've 2 tables (well, 3, 1 user table id, name, email etc.. that's bog standard).

table 1:

questions: id (auto inc) user_id (int) question (text) parent (int) 

table 2:

map_user_question_vote: question_id (int) user_id (int) vote (int) 

it's question , various anwsers site.

so user posts question , parent set 0

when user posts reply, goes same table, parent field has id of parent question.

every user presented vote button on questions , comments. (no downvoting or undoing).

what need comment, find out vote total , parent questions vote total too, single user

this have far:

select   questions.id , questions.question , coalesce(sum(qv.vote), 0) question_vote_score , coalesce(sum(cv.vote), 0) comment_vote_score , parent.id parentid , parent.title parentquestion  `questions`  join `questions` `parent` on `questions`.`parent` = `parent`.`id`  join `map_user_question_vote` `cv` on        `cv`.`question_id` = `questions`.`id`  join `map_user_question_vote` `qv` on       `qv`.`question_id` = `parent`.`id`  questions.user_id = 1   , questions.parent  > 0  group questions.id  limit 5 

what's happening, appears combining 'vote' totals, regardless of they're coming from, questions or comments.

any sql gurus out there want me keep hair? :)

edit: added http://sqlfiddle.com/#!9/b5d08/1

if join tables way , use group in main query, first join records of map_user_question_vote table (twice) records of questions table , sum calculated later same question groups (although column names different, values same).

you need join subqueries first calculate sum of votes. i.e.:

select   questions.id , questions.question , parent.id parentid , parent.question parentquestion , coalesce(qv.sum_vote, 0) parent_question_vote_score , coalesce(cv.sum_vote, 0) comment_vote_score  `questions`  join `questions` `parent` on      `questions`.`parent` = `parent`.`id`  left join (select `question_id`, sum(`vote`) `sum_vote` `map_user_question_vote` group `question_id`) `cv` on            `cv`.`question_id` = `questions`.`id`  left join (select `question_id`, sum(`vote`) `sum_vote` `map_user_question_vote` group `question_id`) `qv` on           `qv`.`question_id` = `parent`.`id`  questions.user_id = 1   , questions.parent  > 0  limit 5 

see results in sql fiddle here.