CODEWITHSUNDEEP
mysqlsql
Luca Garbin
Luca Garbin

Reputation: 61

Mysql query three tables

I'm trying to write a query that join three tables. As you can see on the image below I have:

I need to get a result table that groups expenses by category typology (RESULT TABLE on the image).

enter image description here

select users.email,
(
    SELECT IFNULL(sum(expenses.amount),0)
    FROM expenses
    JOIN expense_categories as cat
    ON cat.id  = expenses.category_id
    AND cat.type = 'home'
    WHERE expenses.user_id = users.id
) as 'home expenses'
from users

This query works correctly, but I'm sure isn't the right way to do that.

Can you help me?

Thank you so much

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

The query you want has joins and one aggregation:

SELECT u.id, u.email,
       SUM(CASE WHEN ec.type = 'home' THEN e.amount ELSE 0 END) as home, 
       SUM(CASE WHEN ec.type = 'job' THEN e.amount ELSE 0 END) as job 
FROM users u LEFT JOIN
     expenses e
     ON u.id = e.user_id LEFT JOIN
     expense_categories ec
     ON ec.id  = e.category_id
GROUP BY u.id, u.email;

Upvotes: 2

Related Questions