MySql Syntax

[Jonob]

Hi all,

 

New to this php and mysql thing, but getting there slowly but surely.

 

I am running mysql 5.05

 

I have a query as follows, which works 100%:

 

 

SELECT i.*,c.name,
			(SELECT SUM(amount_inc)
			FROM income_detail
			WHERE invoice_id = i.invoice_id) AS paid
		FROM invoice i
		LEFT JOIN 
			counterpart c on i.counterpart_id = c.counterpart_id
		WHERE 
			i.is_deleted = 'N' 
			AND i.company_id = 1
			AND i.counterpart_id = 1
		ORDER BY i.date

 

However, what I would like to do, is limit the results to cases where i.amount_inc>paid

 

I tried the following, but it doesnt like the way that I referenced the 'paid' part of the code.

 

SELECT i.*,c.name,
			(SELECT SUM(amount_inc)
			FROM income_detail
			WHERE invoice_id = i.invoice_id) AS paid
		FROM invoice i
		LEFT JOIN 
			counterpart c on i.counterpart_id = c.counterpart_id
		WHERE 
			i.is_deleted = 'N' 
			AND i.company_id = 1
			AND i.counterpart_id = 1
			AND i.amount_inc > paid --This is the new line
		ORDER BY i.date

 

What am I missing?

[revraz]

Does it actually return an error?

[waterssaz]

The following should work:

SELECT i.*,c.name,

(SELECT SUM(amount_inc)

FROM income_detail

WHERE invoice_id = i.invoice_id) AS paid

FROM invoice i

LEFT JOIN

counterpart c on i.counterpart_id = c.counterpart_id

WHERE

i.is_deleted = 'N'

AND i.company_id = 1

AND i.counterpart_id = 1

AND i.amount_inc > (SELECT SUM(amount_inc)

FROM income_detail

WHERE invoice_id = i.invoice_id)

ORDER BY i.date

 

You cannot simply reference > 'paid' as this is simply an alias and is not a variable to store data

[fenway]

Not without a HAVING clause.