Unknown Fields from SubQueries

[SupaMonkey]

Hey Guys,

So here's the thing, I need to draw up an outstanding amount based on 5 tables information:

credit_notes: credit data

- credit_note_products

invoices: debit data

- invoice_products

payments: credit data

 

So this is what I have:

SELECT
(SELECT ROUND(SUM(((sell_price*quantity)-((sell_price*quantity)*(discount/100)))*1.14),2)
FROM invoice_products 
INNER JOIN invoices ON (invoices.id=invoice_products.invoice_id)
WHERE invoice_id=invoices.id and customer_id=127) AS outstanding,
(SELECT SUM(credit_amount*quantity) 
FROM credit_note_products 
INNER JOIN credit_notes ON (credit_notes.id=credit_note_products.credit_note_id)
WHERE customer_id=127) AS credit_notes,
(SELECT	amount
FROM customer_payments 
WHERE customer_id=127) AS payments,
outstanding-credit_notes-payments AS balance_brought_forward

 

Query works fine if I leave out the last line above. As soon as I try and subtract the fields from each other- thats where the problem lies...

[Mchl]

What is the error message?

 

Oh, and you can't use aliases you're just defined in defining another column. So you'd need:

(SELECT ROUND(SUM(((sell_price*quantity)-((sell_price*quantity)*(discount/100)))*1.14),2)
FROM invoice_products
INNER JOIN invoices ON (invoices.id=invoice_products.invoice_id)
WHERE invoice_id=invoices.id and customer_id=127) - (SELECT SUM(credit_amount*quantity)
FROM credit_note_products
INNER JOIN credit_notes ON (credit_notes.id=credit_note_products.credit_note_id)
WHERE customer_id=127) - (SELECT   amount
FROM customer_payments
WHERE customer_id=127) AS balance_brought_forward

 

Which obviously doesn't make much sense. It's better to calculate balance_brought_forward in your PHP script.

[SupaMonkey]

Unknown column 'outstanding' (because its a column alias)...

[fenway]

Unknown column 'outstanding' (because its a column alias)...

Then you'll need to move this condition to the HAVING clause.