POSTGRESQL Self Join Create New Column With Particular Condition

Question

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

INSERT INTO accounts
  ("id", "parrent_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 parrent_account is null, an account doesn't have a merchant/group.

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

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

Expected result:

id	parrent_account	merchant_type	name	MERCHANT	GROUP
1	14056	outlet	RAA CHA SUKI & BBQ NIPAH MAL	RAA CHA	Champ Group
2	14056	outlet	RAA CHA SUKI & BBQ SUNTER MALL	RAA CHA	Champ Group
3	14056	outlet	RAA CHA SUKI & BBQ BAYWALK PLUIT	RAA CHA	Champ Group
3499	NULL	MERCHANT	Kopi Kotak
3500	3499	OUTLET	Kopi Kotak Tebet	Kopi Kotak
14052	NULL	GROUP	Champ Group
14056	14052	MERCHANT	RAA CHA		Champ Group

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.parrent_account = v_merchant.id
left join accounts v_group on v_merchant.parrent_account = v_group.id;

id	parrent_account	merchant_type	name	merchant	group
3	14056	outlet	RAA CHA SUKI & BBQ BAYWALK PLUIT	RAA CHA	Champ Group
2	14056	outlet	RAA CHA SUKI & BBQ SUNTER MALL	RAA CHA	Champ Group
1	14056	outlet	RAA CHA SUKI & BBQ NIPAH MAL	RAA CHA	Champ Group
3500	3499	OUTLET	Kopi Kotak Tebet	Kopi Kotak
14052		GROUP	Champ Group
14056	14052	MERCHANT	RAA CHA	Champ Group
3499		MERCHANT	Kopi Kotak

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.parrent_account = v_merchant.id
left join accounts v_group on v_merchant.merchant_type in ('merchant', 'MERCHANT') and v_merchant.parrent_account = v_group.id;

id	parrent_account	merchant_type	name	merchant	group
3	14056	outlet	RAA CHA SUKI & BBQ BAYWALK PLUIT	RAA CHA	Champ Group
2	14056	outlet	RAA CHA SUKI & BBQ SUNTER MALL	RAA CHA	Champ Group
1	14056	outlet	RAA CHA SUKI & BBQ NIPAH MAL	RAA CHA	Champ Group
3500	3499	OUTLET	Kopi Kotak Tebet	Kopi Kotak
14052		GROUP	Champ Group
14056	14052	MERCHANT	RAA CHA
3499		MERCHANT	Kopi Kotak

View on DB Fiddle

Answer 1

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.parrent_account = v_merchant.id
left join accounts v_group on v_merchant.merchant_type in ('merchant', 'MERCHANT') and v_merchant.parrent_account = v_group.id
left join accounts v_group2 on v_outlet.merchant_type in ('merchant', 'MERCHANT') and v_outlet.parrent_account = v_group2.id

DB Fiddle

Answer 2

The results of your first attempt as per the DBFiddle responses are

which differ from the results you intended to obtain in having null shown instead of empty Strings where there is no value. You can explicitly list your fields and use COALESCE(<yourfield>, '') to convert those nulls into empty strings:

select  v_outlet.id
, v_outlet.parrent_account
, v_outlet.merchant_type
, COALESCE(v_outlet.name, '') AS name
, CASE WHEN v_group.name IS NOT NULL THEN COALESCE(v_merchant.name, '') ELSE '' END AS merchant
, CASE WHEN v_group.name IS NULL THEN COALESCE(v_merchant.name, '') ELSE '' END AS group
from accounts v_outlet
left join accounts v_merchant on v_outlet.parrent_account = v_merchant.id
left join accounts v_group on v_merchant.parrent_account = v_group.id
order by v_outlet.id

See: https://www.db-fiddle.com/f/9BStxo95osxAd6cwwefNtS/2