POSTGRESQL Self Join Create New Column With Particular Condition

CREATE TABLE accounts (
  "id" INTEGER,
  "parent_account" INTEGER,
  "merchant_type" VARCHAR(8),
  "name" VARCHAR(32)
);

INSERT INTO accounts
  ("id", "parent_account", "merchant_type", "name")
VALUES
  (1, 14056, 'outlet', 'RAA CHA SUKI & BBQ NIPAH MAL'),
  (2, 14056, 'outlet', 'RAA CHA SUKI & BBQ SUNTER MALL'),
  (3, 14056, 'outlet', 'RAA CHA SUKI & BBQ BAYWALK PLUIT'),
  (3499, NULL, 'MERCHANT', 'Kopi Kotak'),
  (3500, 3499, 'OUTLET', 'Kopi Kotak Tebet'),
  (14052, NULL, 'GROUP', 'Champ Group'),
  (14056, 14052, 'MERCHANT', 'RAA CHA');

If parent_account is null, an account doesn't have a merchant/group.

If merchant_type is outlet with parent_account, the id (inside parent_account) will refer to a merchant.

If merchant_type is merchant with parent_account, the id (inside parent_account) will refer to a group.

Expected result:

Query #1 and result:

(CHAMP GROUP in row 6 should be in the group field.)

select v_outlet.*
, v_merchant.name as merchant
, v_group.name as group
from accounts v_outlet
left join accounts v_merchant on v_outlet.parent_account = v_merchant.id
left join accounts v_group on v_merchant.parent_account = v_group.id;

View on DB Fiddle

Query #2 and result:

(In row 6 RAA CHA has a group, but the result is null.)

select v_outlet.*
, v_merchant.name as merchant
, v_group.name as group
from accounts v_outlet
left join accounts v_merchant on v_outlet.merchant_type in ('outlet', 'OUTLET') and v_outlet.parent_account = v_merchant.id
left join accounts v_group on v_merchant.merchant_type in ('merchant', 'MERCHANT') and v_merchant.parent_account = v_group.id;

View on DB Fiddle

You need an extra join

1. if the account is an outlet, get its merchant
2. using merchants from #1, get their group
3. if the account is a merchant, get its group

select v_outlet.*
, v_merchant.name as merchant
, coalesce(v_group.name, v_group2.name) as group
from accounts v_outlet
left join accounts v_merchant on v_outlet.merchant_type in ('outlet', 'OUTLET') and v_outlet.parent_account = v_merchant.id
left join accounts v_group on v_merchant.merchant_type in ('merchant', 'MERCHANT') and v_merchant.parent_account = v_group.id
left join accounts v_group2 on v_outlet.merchant_type in ('merchant', 'MERCHANT') and v_outlet.parent_account = v_group2.id

DB Fiddle