Grouping/Joining The Rows Based Based on Abs Amount || Oracle SQL

Question

I have a source table XXTB_JE_TRX like below:

ENTITY	HEADER_ID	NET_AMT
301	10101	-30
301	10101	-50
301	10101	-20
401	10101	30
402	10101	50
302	10101	20

and I want output (Find Related Entity) like this:

ENTITY	HEADER_ID	NET_AMT	RELATED_ENTITY
301	10101	-30	401
301	10101	-50	402
301	10101	-20	302
401	10101	30	301
402	10101	50	301
302	10101	20	301

This is what I've tried:

WITH t1 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt < 0
), t2 AS (
    SELECT
        entity,
        header_id,
        net_amt
    FROM
        XXTB_JE_TRX
    WHERE
        net_amt > 0
)
SELECT
    t1.entity,
    t1.header_id,
    t1.net_amt,
    t2.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt)
UNION ALL
SELECT
    t2.entity,
    t2.header_id,
    t2.net_amt,
    t1.entity related_entity
FROM
    t1,
    t2
WHERE
        t1.header_id = t2.header_id
    AND abs(t1.net_amt) = abs(t2.net_amt);

Is it a right way to do it? Can this be achieved without UNION ALL?

Answer 1

Sure, this ought to work:

select a.entity_id, a.header_id, a.net_amt, b.entity_id as related_entity
  from xxtb_je_trx a join xxtb_je_trx b on a.header_id = b.header_id and a.net_amt = b.net_amt * -1;

You can do an outer join if there won't always be a related_entity. And you could do -b.net_amt instead of b.net_amt * -1.