CODEWITHSUNDEEP
postgresqljoinself-join
Yogyakartas
Yogyakartas

Reputation: 119

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:

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

Upvotes: 0

Views: 77

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76943

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

enter image description here

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 columns and use COALESCE(<yourfield>, '') to convert those nulls into empty strings:

select  
    v_outlet.id
    , v_outlet.parent_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.parent_account = v_merchant.id
left join 
    accounts v_group on v_merchant.parent_account = v_group.id
order by 
    v_outlet.id

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

enter image description here

Upvotes: 0

JGH
JGH

Reputation: 17906

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

Upvotes: 2

Related Questions