MySQL Get score of each user in each category

Vote count:

0

basically i have the following tables:

users (id)

question_categories(id)

questions(id, category)

answers(id, user, question, score)

My problem is to get the total score of each user in each category in order to filter them based on the score in each category.

I expect a result table like (user, score_cat_1, score_cat_2, ...). I tried this option by building the table from PHP to add each category score column, but there is a join subquery I have to run on each category column I want to add. Something like

SELECT u.id, SUM(cat1.score) AS score_cat_1, ...
FROM users u
LEFT JOIN (
     SELECT score, category, user
     FROM answers a
     JOIN questions q ON a.question=q.id AND q.category=1
) AS cat1 ON u.id=cat1.user
...

So I fear a performance issue with the size of that table and the number of categories. Is there a better approach?

Thanks

asked 52 secs ago

atoo

16

MySQL Get score of each user in each category