SQL Query Help for Chart Data

Here is our goal:

We want to select each month and the revenue gained for each month, for a particular user.

Here are our tables

product, purchase, user, months

user 1..* product, product 1..* purchase

product has a column 'user_id' and purchase has a column 'product_id'

months is just a table that contains each month as a string. Currently, we are using this to do some left joins as you can see below.

SELECT months.name, IFNULL(sum(purchase.price), 0) as revenue
FROM months
    LEFT JOIN purchase
        ON DATE_FORMAT(purchase.purchase_date, '%M') = months.name
        AND DATE_FORMAT(purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
        AND purchase.status = 2
GROUP BY name
ORDER BY months.id ASC;

Which works great for ALL of the users and ALL of the purchases made this month (Purchase status of 2 means complete). The next part is how do I filter this based on a user id? The table 'product' has the user_id we're looking for, and table purchase has a 'product_id'. Everytime I try something it either does nothing or it removes all of the null values, which we don't want.

ATTEMPT @NathanialWools The where clause will remove all of the rows with a revenue of zero (or null, because of the IFNULL statement). This is what I tried:

SELECT name, IFNULL(sum(purchase.price), 0) as revenue
FROM months
    LEFT JOIN purchase
        ON DATE_FORMAT(purchase.purchase_date, '%M') = months.name
        AND DATE_FORMAT(purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
        AND purchase.status = 2
    LEFT JOIN product
        ON product.id = purchase.product_id
WHERE product.user_id = 1
GROUP BY 开发者_如何学运维name
ORDER BY months.id ASC;

---

If you have a list of users you could start with that, join to months, then join to product. (if you don't just start with product).

SELECT u.user_id, m.name, IFNULL(sum(p.price), 0) as revenue 
FROM user u
CROSS JOIN months m
LEFT JOIN product d
    ON u.user_id = d.user_id
LEFT JOIN purchase p
    ON DATE_FORMAT(p.purchase_date, '%M') = m.name
    AND DATE_FORMAT(p.purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
    AND p.product_id = d.product_id
    AND p.status = 2
WHERE u.user_id = <user you care about>
GROUP BY u.user_id, m.name
ORDER BY m.id ASC;

---

LEFT JOIN on product, and add a WHERE clause where user_id is null or user_id is equal to your value.

---

What did you try?

SELECT months.name, u.user_id, sum(IFNULL(purchase.price, 0)) as revenue
FROM months
    LEFT JOIN purchase pu
        ON DATE_FORMAT(purchase.purchase_date, '%M') = months.name
        AND DATE_FORMAT(purchase_date, '%Y') = DATE_FORMAT(CURRENT_DATE, '%Y')
        AND purchase.status = 2
    LEFT JOIN product pr
        ON pr.product_id = pr.product_id
    LEFT JOIN user u
        ON u.user_id = pr.user_id
GROUP BY pu.name, u.user_id
ORDER BY months.id ASC;

---

An inner-select Seems to do the trick. The left join on the purchase alone was not enough filtering. Here is the solution that works for me:

SELECT m.name, IFNULL(sum(b.price), 0) as revenue
FROM months as m
LEFT JOIN (SELECT price, purchase_date, status
           FROM purchase, product
           WHERE purchase.product_id = product.id
           AND product.user_id = 1) as b
    ON DATE_FORMAT(b.purchase_date, '%M') = m.name
    AND DATE_FORMAT(b.purchase_date, '%Y') = DATE_FORMAT(CURRENT_TIMESTAMP, '%Y')
    AND status = 2
GROUP BY m.name
ORDER BY m.id ASC

Where you would obviously change the '1' based on which user you wanted to look at.

Thanks everyone for pitching in, made debugging faster.