CODEWITHSUNDEEP
phpmysql
Ralph
Ralph

Reputation: 897

Query works in workbench but not in php

I'm trying to assign a variable a value based on a subquery in the where statement. The problem is that it doesn't work in php but in the workbench the query runs fine. I don't get any errors in php and it returns the correct pay ids but the variable field returns empty.

SELECT pay_id, @available AS amount_available
FROM tblpayments payments
WHERE customer_id = 9
AND (
    @available := (pay_amount - (
        SELECT if(sum(applied_amount) IS NULL, 0, sum(applied_amount))
        FROM tblxref_pmt_chg xref WHERE xref_pay_id = payments.pay_id
    ))
) > 0

Upvotes: 0

Views: 480

Answers (1)

gen_Eric
gen_Eric

Reputation: 227280

Why not try using a JOIN instead of a subquery/variable?

Something like this:

SELECT pay_id, (pay_amount - COALESCE(SUM(applied_amount), 0)) AS amount_available
FROM tblpayments payments
LEFT JOIN tblxref_pmt_chg xref ON xref_pay_id = payments.pay_id
WHERE customer_id = 9
GROUP BY payments.pay_id
HAVING amount_available > 0

Upvotes: 3

Related Questions