Combining two mysql query returns ok instead of rows

Question

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.

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.

https://stackoverflow.com/questions/20284528/how-to-concat-two-columns-into-one-with-the-existing-column-name-in-mysql/20285571 — Lahiru Madusanka, Aug 22 '18 at 07:09
"check that ONLY rows are returned where there is no match between the invoice and payment table" -- don't quite get that. In any case, you don't need HAVING since you're not GROUPing BY anything, so you can put all conditions in the WHERE clause. — memo, Aug 22 '18 at 07:14
@memo I just meant that if there is a match between the tables - those rows shouldnt be returned and only the non matching rows should return. Okay I will try putting them in the where clause — Masnad Nihit, Aug 22 '18 at 07:17
@LahiruMadusanka the link you posted has nothing to do with his. — Masnad Nihit, Aug 22 '18 at 07:20
Please simplify your query to the minimum required to demonstrate the problem, using fake column names etc. Specifically, don't copy-paste your actual query into the question. Not only is it hard to read and mostly noise, but future visitors won't get as much value if the question isn't posed in its most general form. — Bohemian, Aug 23 '18 at 03:03
@Bohemian I figured out the answer, and I will put it below, can you reopen it. — Masnad Nihit, Aug 23 '18 at 10:13

score 1 · Accepted Answer · answered Aug 24 '18 at 13:51

Try seeing if the other table exists or not using 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;

Combining two mysql query returns ok instead of rows

1 Answers1