CODEWITHSUNDEEP
mysql
Masnad Nihit
Masnad Nihit

Reputation: 1996

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.

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.

Upvotes: 1

Views: 591

Answers (1)

mr aurora
mr aurora

Reputation: 189

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;

Upvotes: 1

Related Questions