Combining two mysql query returns ok instead of rows

I have a query in which I return some information regarding an invoice, I take that invoice and compare it to another table "payment" to see if that invoice (fullamount -fullpaid) exists in the other table and if it does some function should not run in my backend code.

SELECT a.status, a.rf_reference, a.payor_orig_id , a.gross_amount + a.type_related_amount as fullamount,
a.gross_paid + a.type_related_paid as fullpaid
FROM invoice a
where a.type = 3 and
a.status in (10, 30) and
a.UPDATE_DT is null
having fullamount > fullpaid
order by a.ORIG_ID;


The above query returns

status| rf_reference | payor_orig_id | fullamount | fullpaid
30 RF123456 212 1000 200


So now I take the above information and pass it onto another query to see if a row field matches.

I pass it on like this

select *
from payment
where
payor_orig_id = 212 and
rf_reference = RF123456 and
payment_amount = (1000-200) and
status = 10 and
INSERT_DT BETWEEN DATE_SUB(NOW(), INTERVAL 185 DAY) AND NOW() and
UPDATE_DT IS NULL;


So now the above code will return a row by which basically I do not run my backend function.

Since this are two separate query I would like to combine them to one where I make sure that I add a having statement and check that ONLY rows are returned where there is no match between the invoice and payment table.

having

SELECT a.status, a.rf_reference, a.payor_orig_id , a.gross_amount + a.type_related_amount as fullamount,
a.gross_paid + a.type_related_paid as fullpaid,
(select b.payment_amount
from payment b
where
b.payor_orig_id = a.payor_orig_id and
b.rf_reference = a.rf_reference and
b.status = 10 and
b.INSERT_DT BETWEEN DATE_SUB(NOW(), INTERVAL 185 DAY) AND NOW() and
b.UPDATE_DT IS NULL) as payment_amount
FROM invoice a
where a.type = 3 and
a.status in (10, 30) and
a.UPDATE_DT is null
having fullamount > fullpaid and
(fullamount - fullpaid ) <> payment_amount
order by a.ORIG_ID;


The above query returns "OK" which is odd since I am not sure how to debug it.

1 Answer
1

Try seeing if the other table exists or not using NOT EXIST

NOT EXIST

SELECT a.* ,
a.gross_amount + a.type_related_amount as fullamount,
a.gross_paid + a.type_related_paid as fullpaid
FROM invoice a
where a.type = 3 and
a.status in (10, 30) and
a.UPDATE_DT is null and
NOT EXISTS ( select *
from payment
where
payor_orig_id = a.payor_orig_id and
rf_reference = a.rf_reference and
payment_amount = ((a.gross_amount + a.type_related_amount) - (a.gross_paid + a.type_related_paid)) and
status = 10 and
INSERT_DT BETWEEN DATE_SUB(NOW(), INTERVAL 185 DAY) AND NOW() and
UPDATE_DT IS NULL )
having fullamount > fullpaid
order by a.ORIG_ID;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.