sqlsubqueryimpalain-subquery

Subquery with 2 parameters in SQL

I have a table in SQL which looks like this:

[Sample Table[1]

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:

Resultant Table

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.

Solution

  • 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.