Subquery with 2 parameters in SQL

Question

I have a table in SQL which looks like this:

[

Now, I want the resultant table based on 2 conditions:

1. Prev_trans_id should match the transactions_ID

2. Only those entries should come where mod of Amount value is not equal.

The resultant table should like this:

SO, in the resultant table, I dont want row with Transcation_ID as 104 since the mod of amount is same. $1 was paid and $1 was refunded.

I was able to do 1st part of it but not able to do 2nd part as I am new to SQL. This is my code for the 1st part:

select * from sample_table
where prev_trans_id in 
        (select transaction_id from sample_table)

If I can get the 2nd condition also incorporated in the same query, it would be very helpful.

Answer 1

Use a JOIN, not IN

SELECT t1.*
FROM sample_table AS t1
JOIN sample_table AS t2 
    ON t1.prev_trans_id = t2.transaction_id AND t1.amount != -1 * t2.amount

BTW, it's not mod of the amounts, it's the negation of the amounts that you want to compare.

Answer 2

There's no need to use a subquery here as it can be achieved with a basic select ... from ... where ... query on table1 and table2. Please see the query below:

select table2.*
from sample_table table1, sample_table table2
where table1.transaction_id = table2.prev_trans_id
and (table1.amount - table2.amount) <> 0