Oracle SQL Self-joins in the same statement

Question

I'm trying to achieve the following. Any help will be highly appreciated!

1.In the first table, do a self-join on a main deal with its corresponding offset deal 2.In the second table, find all the main deals where the prices don't match with the corresponding offset deals.

I don't think the second self-join works. That's supposed to link the main and the offset deals in the prices table and find cases where the prices don't match for certain price dates.

SELECT *
FROM transactions tran1 transactions tran2
    ,prices pr1
    ,prices pr1
WHERE tran1.tran_type = 1 --Original deal 
    AND tran2.tran_type = 2 -- Offset deal 
    AND tran1.tran_num = tran2.offset_tran_num
    AND tran1.ins_num = pr1.ins_num
    AND tran2.ins_num = pr2.ins_num
    AND pr1.ins_num = pr2.ins_num
    AND pr1.profile_num = pr2.profile_num
    AND pr1.price_date = pr2.price_date
    AND pr1.value != pr2.value

Answer 1

This statement could benefit from using proper join syntax, instead of this older style - immediately on trying to convert it you can see the alias 'pr1' is used twice, you meant to use 'pr2'

Given that being correct, (and assuming you may need some help on converting it to a modern syntax) you have :

SELECT *
FROM transactions tran1
inner join prices pr1 on pr1.ins_num = tran1.ins_num 
inner join prices pr2 on pr2.ins_num = pr1.ins_num
                      and pr2.profile_num = pr1.profile_num 
                      and pr2.price_date = pr1.price_date
                      and pr2.value != pr1.value
inner join transactions tran2 on tran2.ins_num = pr2.ins_num 
                      and tran2.offset_tran_num = tran1.tran_num 
WHERE tran1.tran_type = 1 --Original deal
AND tran2.tran_type = 2 -- Offset deal 

It's easier to read but we would need more information, input + expected output to confirm if it is what you intended.