0

How to filter data in the below query using where clause for Balance column. I'm getting the error [Unknown column "Balance" in "where clause"] when i use Balance in where condition.

select *,(it.Total - p.Amount) as Balance
from invoices i
left outer join invoice_items it
    on i.ID=it.InvoiceID
left outer join payment p
    on p.InvoiceID=it.InvoiceID 
where Balance!=0;

And also, when there is no matching payment record found, instead of displaying the null value in Balance column, i need the Total value of the invoice_items table.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Uma
  • 229
  • 1
  • 3
  • 9
  • `where (it.Total - p.Amount) != 0;` should works: http://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql – dani herrera Sep 18 '12 at 08:20

2 Answers2

1

You can't use an alias in a where clause.

rewrite

where (it.Total - p.Amount) <> 0

and for the other part

  select 
   (case when p.PaymentId IS NULL 
    then it.Total
    else
    (it.Total - p.Amount) 
   end)

or. COALESCE means : if p.Amount is null, use 0. Else use p.Amount

select it.Total - COALESCE(p.Amount, 0)

FINALLY

select i.*,(it.Total - COALESCE(p.Amount, 0)) as Balance
from invoices i
left outer join invoice_items it
    on i.ID=it.InvoiceID
left outer join payment p
    on p.InvoiceID=it.InvoiceID 
where it.Total - COALESCE(p.Amount, 0) <> 0;
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
0

Try this:

select *,(it.Total - p.Amount) as Balance
from invoices i
left outer join invoice_items it
    on i.ID=it.InvoiceID
left outer join payment p
    on p.InvoiceID=it.InvoiceID 
where (it.Total - p.Amount) <> 0;

You cannot use an alias in where clause, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

heretolearn
  • 6,387
  • 4
  • 30
  • 53