MySQL Query which require multiple joins

Question

I have a system that is used to log kids' their behavior. If a child is naughty it is logged as negative and if it has a well behaviour it is logged as positive.

For instance - if a child is rude it gets a 'Rude' negative and this is logged in the system with minus x points.

My structure can be seen in this sqlfiddle - http://sqlfiddle.com/#!9/46904

In the users_rewards_logged table, the reward_id column is a foreign key linked to either the deductions OR achievements table depending on the type of column. If type is 1 is a deduction reward, if the type value is 2 is a achievement reward.

I basically want a query to list out something like this:

  +------------------------------+
  |   reward   | points  | count |
  +------------------------------+
  | Good Work  |   100   |   1   |
  |    Rude    |   -50   |   2   |
  +------------------------------+

So it tallys up the figures and matches the reward depending on type (1 is a deduction, 2 is a achievement)

What is a good way to do this, based on the sqlfiddle?

Answer 1

Here's a query that gets the above desired results:

SELECT COALESCE(ua.name, ud.name) AS reward,
  SUM(url.points) AS points, COUNT(url.logged_id) AS count
FROM users_rewards_logged url
LEFT JOIN users_deductions ud
  ON ud.deduction_id = url.reward_id
  AND url.type = 1
LEFT JOIN users_achievements ua
  ON ua.achievement_id = url.reward_id
  AND url.type = 2
GROUP BY url.reward_id, url.type

Your SQLFiddle had the order of points and type in the wrong order for the table users_rewards_logged.

Here's the fixed SQLFiddle with the result:

reward      points  count
Good Work      100      1
Rude           -50      2