select join in the same table-CodePudding

I have a table like this

TRX_NUMBER is a invoice, and this field have a return number inside of the invoice.

And I want to select table and join to the same table use CUSTOMER_TRX_ID and PREVIOUS_CUSTOMER_TRX_ID as the connection (ON)

And the result what I want

Can you help me about this ?

CodePudding user response：

Here's one option:

Sample data:

SQL> with invoice (customer_trx_id, trx_number, previous_customer_trx_id) as
  2    (select 81196, 'ARR05-09', 22089 from dual union all
  3     select 22089, 'IJU86-09', null  from dual union all
  4     select 13931, 'IJU07-09', null  from dual
  5    )

Query begins here:

  6  select a.trx_number, b.trx_number as retur
  7  from invoice a left join invoice b on a.customer_trx_id = b.previous_customer_trx_id
  8  where not exists (select null
  9                    from invoice c
 10                    where c.customer_trx_id = a.previous_customer_trx_id);

TRX_NUMBER      RETUR
--------------- --------
IJU86-09        ARR05-09
IJU07-09

SQL>

CodePudding user response：

Use an alias to take the same table multiple times.

For example we have a table INVOICE:

SELECT t1.TRX_NUMBER AS TRX_NUMBER, t2.TRX_NUMBER AS RETURN
FROM INVOICE AS t1
JOIN INVOICE AS t2 t1.CUSTOMER_TRX_ID = t2.PREVIOUS_CUSTOMER_TRX_ID