PostgresSQL default statement for join

Question

I have a transactions table, each transaction has an address field which references a row in an address table, each address in the address table has a coinID.

I want to get a sum of all transactions for EVERY coin for a specific user. I don't care about ownership, I want to display every coin in the table regardless if users own it or not.

The problem I have is that if a user has 0 transactions or addresses that belong to a specific coin it is completely missing from the result. I need all coins in a coin table that have 0 transactions or addresses to return with a sum of 0.

SELECT 
        CASE WHEN SUM(transactions.amount) IS NULL THEN 0 ELSE SUM(transactions.amount) END AS balance
FROM coins
Left outer  JOIN addresses ON addresses.coin_id = coins.id
Left outer  JOIN transactions ON transactions.address = addresses.address
Left outer JOIN users ON transactions.user_id = users.id
WHERE users.email = '[email protected]'

I have tried quite a few combinations of queries but no matter what I do I get 1 of two options

• rows are only returned for coins that the user has a transaction/address for
• rows are returned but contain the balance of all transactions in the database not just the ones belonging to that user.

Answer 1

I think the problem is the WHERE. If no user was joined, no e-mail address can match and you get the empty set. Try to CROSS JOIN the user in question and then LEFT JOIN the addresses and transactions.

SELECT c.id,
       coalesce(sum(t.amount), 0)
       FROM coins c
            CROSS JOIN (SELECT u.id
                               FROM users u
                               WHERE u.email = '[email protected]') u
            LEFT JOIN addresses a
                      ON a.coin_id = c.id
            LEFT JOIN transactions t
                      ON t.address = a.address
                         AND t.user_id = u.id
            GROUP BY c.id;