Counting the same column in one table under a different alias?
I have two tables (well, 3, one is a users table with id, name, email, etc., but this is a standard swamp).
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)
This is a question and various sites anwsers.
So the user is submitting the question and the parent is 0
When another user submits a response, they navigate to the same table, but the parent field has the parent question ID.
Each user is provided with a voting button for questions and comments. (no downvoting or undoing).
What I need to do is get a comment, find out its number of votes, and then the total number of parental votes for just one user.
This is what I have so far:
SELECT
questions.id
, questions.question
, COALESCE(SUM(qv.vote), 0) as question_vote_score
, COALESCE(SUM(cv.vote), 0) as comment_vote_score
, parent.id as parentId
, parent.title as parentQuestion
from `questions`
join `questions` as `parent` on `questions`.`parent` = `parent`.`id`
join `map_user_question_vote` as `cv` on
`cv`.`question_id` = `questions`.`id`
join `map_user_question_vote` as `qv` on
`qv`.`question_id` = `parent`.`id`
where questions.user_id = 1
and questions.parent > 0
group by questions.id
limit 5
What appears to be happening is a pooling of all the "voting" results, no matter where they come from, questions or comments.
Any SQL gurus want to help me keep my hair? :)
EDIT: Added http://sqlfiddle.com/#!9/b5d08/1
source to share
If you join tables this way and use a group by main query, you will first join the table records map_user_question_vote
(twice) to the table records questions
and the sum will be calculated later for the same question groups (although the column names are different, the values will be the same).
You need to join the subqueries where you first calculate the sum of the votes. I.e:.
select
questions.id
, questions.question
, parent.id as parentId
, parent.question as parentQuestion
, COALESCE(qv.sum_vote, 0) as parent_question_vote_score
, COALESCE(cv.sum_vote, 0) as comment_vote_score
from `questions`
join `questions` as `parent` on
`questions`.`parent` = `parent`.`id`
left join (select `question_id`, sum(`vote`) as `sum_vote` from `map_user_question_vote` group by `question_id`) as `cv` on
`cv`.`question_id` = `questions`.`id`
left join (select `question_id`, sum(`vote`) as `sum_vote` from `map_user_question_vote` group by `question_id`) as `qv` on
`qv`.`question_id` = `parent`.`id`
where questions.user_id = 1
and questions.parent > 0
limit 5
See the results in the SQL script here .
source to share
I'm not sure if I am correct, but I think this might be what you are looking for:
SELECT
q.id,
q.user_id,
q.question,
SUM(IF(q.parent > 0, cv.vote, 0)) as comment_vote,
SUM(IF(q.parent = 0, cv.vote, 0)) as question_vote
FROM map_user_question_vote as cv
INNER JOIN questions as q ON q.id = cv.question_id
WHERE q.user_id = 1
GROUP BY q.id, q.user_id, q.parent
;
source to share