'PostgreSQL JOIN - Return NULL for duplicate results in left table
I want to return some value in my query to 'null' if same or duplicates values
My query is like this:
SELECT
partner.nrp as nrp,
partner.name as name,
COALESCE(simp_wajib.savings_amount,0) as simp_wajib,
loan.id as loan_id,
COALESCE(loan.amount,0) as pokok_pinjaman
FROM(
SELECT id as id,
name as name,
nrp as nrp
FROM res_partner
WHERE anggota = True) as partner
RIGHT JOIN(
SELECT anggota_id,
savings_type,
savings_amount
FROM basic_savings
WHERE billing_id = 46
AND savings_type = 2) as simp_wajib on simp_wajib.anggota_id = partner.id
LEFT JOIN(
SELECT id,
anggota_id,
amount,
amount_bunga,
subtotal
FROM kopkar_loan_line
WHERE billing_id = 46) as loan on loan.anggota_id = partner.id
ORDER by name
Output below:
| nrp | name | simp_wajib | loan_id | pokok_pinjaman |
|---|---|---|---|---|
| 12345 | Tony | 20000 | 1 | 100000 |
| 12345 | Tony | 20000 | 2 | 50000 |
Expected output should be:
| nrp | name | simp_wajib | loan_id | pokok_pinjaman |
|---|---|---|---|---|
| 12345 | Tony | 20000 | 1 | 100000 |
| 12345 | Tony | NULL | 2 | 50000 |
I've tried several references but it doesn't work
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
