SQL SELF JOIN return null if not exist

Recently, I got a table A like this:

+----------+-----------+---------------+
| VoucherID| AccountID | TransactionID |
+----------+-----------+---------------+
| 48       | 96        | 10            |
+----------+-----------+---------------+
| 48       | 14        | 10            |
+----------+-----------+---------------+
| 37       | 14        | 88            |
+----------+-----------+---------------+
| 37       | 25        | 88            |
+----------+-----------+---------------+
| 57       | 12        | 30            |
+----------+-----------+---------------+
|.......

I try to SELF JOIN this table by

SELECT account_log.*, reciprocal_account.AccountID as Reciprocal_accountID
FROM A AS account_log
LEFT JOIN A AS reciprocal_account ON account_log.VoucherID = reciprocal_account.VoucherID
WHERE account_log.AccountID <> reciprocal_account.AccountID

I got this

+----------+-----------+---------------+----------------------+
| VoucherID| AccountID | TransactionID | Reciprocal_accountID |
+----------+-----------+---------------+----------------------+
| 48       | 96        | 10            | 14                   |
+----------+-----------+---------------+----------------------+
| 37       | 14        | 88            | 25                   |
+----------+-----------+---------------+----------------------+

What I expect is when VoucherID doesn’t have any reciprocal account, that record will return null, like this

+----------+-----------+---------------+----------------------+
| VoucherID| AccountID | TransactionID | Reciprocal_accountID |
+----------+-----------+---------------+----------------------+
| 48       | 96        | 10            | 14                   |
+----------+-----------+---------------+----------------------+
| 37       | 14        | 88            | 25                   |
+----------+-----------+---------------+----------------------+
| 57       | 12        | 30            | null                 |
+----------+-----------+---------------+----------------------+

Is there any elegant approach? Thanks all.

Answer

Why not just use aggregation?

select transaction_id, voucher_id,
       min(account_id) as account_id,
       nullif(max(account_id), min(account_id)) as reciprocal_account_id
from a
group by transaction_id, voucher_id;

You could also do this using left join:

SELECT account_log.*, reciprocal_account.AccountID as Reciprocal_accountID
FROM A account_log LEFT JOIN
     A reciprocal_account
     ON account_log.VoucherID = reciprocal_account.VoucherID AND
        account_log.AccountID < reciprocal_account.AccountID;

Advertisement

Answer