Query to show status of transactions

Question

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

Unrelated: You shouldn't substitute variables directly into the SQL, you should use a prepared statement with parameters. See https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python — Barmar, Aug 01 '22 at 22:25
In the first query in the UNION, use `INNER JOIN` instead of `LEFT JOIN`. Then you don't have to check `IS NOT NULL`. And in the second query, you don't need `IFNULL(t2.status)`, since all the `t2` columns are guaranteed to be `NULL`. — Barmar, Aug 01 '22 at 22:26
I will rewrite the query with an orm afterwards to prevent any attacks. Also will the group by be an issue since i'm using it to prevent duplicates and get just one transaction? — emmanuel_kb, Aug 01 '22 at 22:34
`GROUP BY` is wrong if there's no aggregation. Use `SELECT DISTINCT` to prevent duplicates. — Barmar, Aug 01 '22 at 22:35

Barmar · Accepted Answer · 2022-08-01T22:36:26.510

0

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

edited Aug 01 '22 at 22:36

answered Aug 01 '22 at 22:31

Barmar

741,623
53
500
612

This works perfectly but if a transaction has both credit and debit transaction types I would like to show only the status of the credit transaction @Barmar – emmanuel_kb Aug 01 '22 at 23:00
I don't think your query does that, even though you said it works. – Barmar Aug 01 '22 at 23:02
That's what made me include the group by but I don't think that will work all the time – emmanuel_kb Aug 01 '22 at 23:05
Right, it selects values from the group arbitrarily, not necessarily the ones you want. And different columns can come from different rows. Most SQL databases won't even allow that use of `GROUP BY`, and MySQL prohibits it when the `ONLY_FULL_GROUP_BY` setting is enabled (the default since 5.7). – Barmar Aug 02 '22 at 13:44
It's possible to do what you want with more subqueries, similar to selecting the row with the max value of a column in each group (see https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1). But It's more complex than I have time to implement here. – Barmar Aug 02 '22 at 13:45

Query to show status of transactions

1 Answers1