Query to show status of transactions

August 2, 2022

I have two tables: requests and responses. If a credit transaction exists in both tables, It’s status should be the status in the responses table. If a credit transaction exists only in the requests table it’s status should be ‘pending’. Now the issue is with debit transactions which is going to have 2 legs: A debit with a request and response then a credit with a request and response. It is only ‘successful’ if it has a debit and credit transactions in both requests and responses and is successful in responses for the credit otherwise it is ‘pending’ or has the status.

Currently this query works but I feel it’s not right. Any help is appreciated

SELECT * FROM (
    SELECT t1.trans_id, t1.transaction_type, t1.trans_date, 
        IF (t1.transaction_type='DEBIT','PENDING',t2.`status`) `status`, t1.wallet_type, t1.amount,t1.created   
    FROM transaction_requests t1 
    LEFT JOIN transaction_responses t2 USING ( trans_id ) 
    WHERE t1.user_id = {repr(user_id)} AND t2.response_id IS NOT NULL 
    GROUP BY trans_id

    UNION ALL

    SELECT t1.trans_id, t1.transaction_type, t1.trans_date, ifnull( t2.STATUS, "PENDING" ) `status`, t1.wallet_type,
        t1.amount,t1.created    
    FROM transaction_requests t1 
    LEFT JOIN transaction_responses t2 USING ( trans_id ) 
    WHERE t1.user_id =  {repr(user_id)} AND t2.response_id IS NULL
) temp  
ORDER BY created DESC 
LIMIT 20

>Solution :

You can combine the queries into a single LEFT JOIN that checks IFNULL() to determine if there’s a matching row, and uses that to get the appropriate status.

SELECT DISTINCT t1.trans_id, t1.transaction_type, t1.trans_date, 
    CASE
        WHEN t2.trans_id IS NULL THEN 'PENDING'
        WHEN t1.transaction_type='DEBIT' THEN 'PENDING'
        ELSE t2.`status`
    END AS `status`, 
    t1.wallet_type, t1.amount,t1.created    
FROM transaction_requests t1 
LEFT JOIN transaction_responses t2 USING ( trans_id ) 
WHERE t1.user_id =  {repr(user_id)}
ORDER BY t1.created DESC
LIMIT 20