mysqlright-join

Having a strange issue using right join in query


I have this MySQL query that is supposed to give me the amount in and out of all bank accounts in the database for the last 30 days. It even fills gaps with the use of a join statement with a calendar table.

However I ran into issues limiting the dataset only to include the statements belonging to a specific account, defined by a account_id field.

Here is the original query that fetches all entries in database for a specific account:

SELECT
    COUNT(transactions.account_id) AS numTransactions,
    calendar.datefield AS date,
    SUM(CASE WHEN transactions.amount < 0 THEN transactions.amount ELSE 0 END) as negativeAmount,
    SUM(CASE WHEN transactions.amount > 0 THEN transactions.amount ELSE 0 END) as positiveAmount
FROM
    transactions
RIGHT JOIN
    calendar
ON
    DATE(transactions.accounting_date) = calendar.datefield
WHERE
    calendar.datefield BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
AND
    transactions.account_id = 1
GROUP BY
    date
ORDER BY
    date DESC;

This will return only the days with values for transactions.amount ignoring the RIGHT JOIN calendar clause.

What am I missing here?


Solution

  • I actually solved it fairly quickly.

    My WHERE clause is on the left table, and not the right (?).

    This worked:

    SELECT
        COUNT(transactions.account_id) AS numTransactions,
        calendar.datefield AS date,
        SUM(CASE WHEN transactions.amount < 0 THEN transactions.amount ELSE 0 END) as negativeAmount,
        SUM(CASE WHEN transactions.amount > 0 THEN transactions.amount ELSE 0 END) as positiveAmount
    FROM
        transactions
    RIGHT JOIN
        calendar
    ON
        DATE(transactions.accounting_date) = calendar.datefield
    AND
        transactions.account_id = 1
    WHERE
        calendar.datefield BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND NOW()
    GROUP BY
        date
    ORDER BY
        date DESC;