Question 238923 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL Self Join Create New Columhj With Particular Condition

I have table :

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 merchant_type is outlet and have parrent_account, the id(inside parrent_account) will refer to merchant.

  • if merchant_type is merchant and have parrent_account, the id(inside parrent_account) will refer to group.

  • if parrent_account null its dont have merchant/group.

the result i expect is :

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

result query1 :
(CHAMP GROUP in rows 6, should be on 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

result query2 :
(should be in rows 6, RAA CHA have 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

can someone help me ? thank you

Tags:

2

Answers


  1. 0

    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 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

    enter image description here

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search