SQL Query with LEFT JOIN Issue

Question

I am having problems with a left join SQL query but can't see why it isn't working. I have 3 tables: customers, purchases and payments, and i'm trying to select customers who's total purchases cost is less than their total payments (i.e. they have a balance greater than 0).

So far, I have the following:

Tables:

Customers

id | Name


Purchases

id | customerid | cost


Payments

id | customerid | paymentamount 

SQL query:

SELECT  a.*, 
COALESCE(b.totalCost , 0) as totalCost, 
COALESCE(c.totalPayments , 0) as totalPayments, 
COALESCE(b.totalCost , 0) - COALESCE(c.totalPayments , 0) AS Balance 
FROM customers a 
LEFT JOIN (SELECT  customerid, SUM(cost) AS totalCost FROM purchases GROUP BY customer) b ON a.id = b.customerid 
LEFT JOIN (SELECT customerid, SUM(paymentamount) AS totalPayments FROM payments GROUP BY customerid) c ON a.id = c.customerid 
WHERE Balance > 0"

When I run the query, I get the error 'Unknown column 'Balance' in 'where clause'' even though I have defined Balance.

Any help is much appreciated. Thanks!

Answer 1

because BALANCE is an ALIAS given on the expression. Use the expression on the WHERE clause instead of the ALIAS

WHERE   COALESCE(b.totalCost , 0) - COALESCE(c.totalPayments , 0) > 0

the other way is to wrap the whole statement with a subquery so you can use the alias on the WHERE clause of the outer query.

The reason why you cannot use ALIAS that is created on the same level of the WHERE clause is because WHERE clause executes first than the SELECT clause where the ALIAS is created.

Here's the SQL Order of Operation:

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

Answer 2

Unfortunately you can't reference an alias on the same "level" where it's defined.

You need to wrap everything into a derived table:

select *
from (
   <your query goes here>
) t
where balance > 0