SQL Query with LEFT JOIN Issue -

i having problems left join sql query can't see why isn't working. have 3 tables: customers, purchases , payments, , i'm trying select customers who's total purchases cost less total payments (i.e. have balance greater 0).

so far, have following:

tables:

customers  id | name   purchases  id | customerid | cost   payments  id | customerid | paymentamount  

sql query:

select  a.*,  coalesce(b.totalcost , 0) totalcost,  coalesce(c.totalpayments , 0) totalpayments,  coalesce(b.totalcost , 0) - coalesce(c.totalpayments , 0) balance  customers  left join (select  customerid, sum(cost) totalcost purchases group customer) b on a.id = b.customerid  left join (select customerid, sum(paymentamount) totalpayments payments group customerid) c on a.id = c.customerid  balance > 0" 

when run query, error 'unknown column 'balance' in 'where clause'' though have defined balance.

any appreciated. thanks!

because balance alias given on expression. use expression on where clause instead of alias

where   coalesce(b.totalcost , 0) - coalesce(c.totalpayments , 0) > 0 

the other way wrap whole statement subquery can use alias on where clause of outer query.

the reason why cannot use alias created on same level of where clause because where clause executes first select clause alias created.

here's sql order of operation:

• from clause
• where clause
• group clause
• having clause
• select clause
• order clause