Query to list all parents of a hierarchical category in column wise with category group

Question

I have 3 Table one is categories

 id    |    name             |    parentid       |   categorygroupid
---------------------------------------------------------------------
  1         Fruits                    0                    1
  2         Citrus Fruits             1                    2
  3         Oranges                   2                    3
  4         Limes                     2                    3
  5         Melons                    1                    2
  6         watermelons               5                    3
  7         vegetables                0                    1
  8         onion                     7                    3
  9         potato                    7                    3
---------------------------------------------------------------------


CREATE TABLE categories
(
    id bigint ,
    name character varying(100) COLLATE pg_catalog."default",
    parentid bigint,
    categorygroupid bigint
)

insert into categories (id,name,parentid,categorygroupid)values('1','Fruits','0','1');
insert into categories (id,name,parentid,categorygroupid)values('2','Citrus Fruits','1','2');
insert into categories (id,name,parentid,categorygroupid)values('3','Oranges','2','3');
insert into categories (id,name,parentid,categorygroupid)values('4','Limes','2','3');
insert into categories (id,name,parentid,categorygroupid)values('5','Melons','1','2');
insert into categories (id,name,parentid,categorygroupid)values('6','watermelons','5','3');
insert into categories (id,name,parentId,categorygroupid)values('7','vegetables','0','1');
insert into categories (id,name,parentid,categorygroupid)values('8','onion','7','3');
insert into categories (id,name,parentid,categorygroupid)values('9','potato','7','3');

and second table is categorygroups

 id     groupname    
 ------------------------
  1     Item Group
  2     Category Type
  3     Category
 ------------------------

CREATE TABLE categorygroups
(
    id bigint ,
    groupname character varying(150) COLLATE pg_catalog."default"
)

insert into categorygroups(id,groupname)values('1','Item Group');
insert into categorygroups(id,groupname)values('2','Category Type');
insert into categorygroups(id,groupname)values('3','Category');

and third table is items

id   |    itemname      |    categoryid
------------------------------------------
1        ABC-ORANGE              3
2        DEF-ORANGE              3
3        AB-WaterMelons          5
4        MN-onion                8
5        PQ-potato               9
------------------------------------------

CREATE TABLE items
(
    id bigint ,
    itemname character varying(250) COLLATE pg_catalog."default",
    categoryid bigint
)

insert into items (id,itemname,categoryid)values('1','ABC-ORANGE','3');
insert into items (id,itemname,categoryid)values('2','DEF-ORANGE','3');
insert into items (id,itemname,categoryid)values('3','AB-Melons','5');
insert into items (id,itemname,categoryid)values('4','MN-onion','8');
insert into items (id,itemname,categoryid)values('5','PQ-potato','9');

I want a help to build a query (mysql/postgres) to get the following result

id | itemname    |  Item Group | Category Type  |   Category
---------------------------------------------------------------
1  | ABC-ORANGE  |   Fruits    | Citrus Fruits  |  Oranges
2  | DEF-ORANGE  |   Fruits    | Citrus Fruits  |  Oranges
3  | AB-Melons   |   Fruits    | Melons         |  watermelons
4  | MN-onion    |   vegetables|                |  onion   
5  | PQ-potato   |   vegetables|                |  potato

---

In result columns Item Group , Category Type and Category are three records ( it will increase ) in categorygroups table and we will show the category or parent category of an item in the above mentioned column.

for eg:- In the above result item ABC-ORANGE assigned a category Oranges and this category have a group Category . so in result category name Oranges will show in Category column and this category ( Oranges ) have a parent ( Citrus Fruits ) and this parent have group ( Category Type ). so the parent category name Citrus Fruits will show in Category Type column.

Answer 1

See working example with fiddle here using the sample data provided. Please share how this works with your real data or additional test samples.

Schema (PostgreSQL v11)

Query #1

WITH all_categories AS (
    SELECT
        c.*,
        cg.groupname
    FROM
        categories c
    INNER JOIN
        categorygroups cg ON cg.id = c.categorygroupid
)
SELECT
    i.id,
    i.itemname,
    COALESCE(ig.name,'') as "Item Group",
    COALESCE(ct.name,'') as "Category Type",   
    c.name as "Category"
FROM
    items i
LEFT JOIN
    all_categories c ON i.categoryid = c.id
LEFT JOIN
    all_categories ct ON c.parentid = ct.id  AND
                         ct.groupname = 'Category Type'
LEFT JOIN
    all_categories ig ON (ct.parentid = ig.id or c.parentid=ig.id ) AND
                         ig.groupname = 'Item Group' 
ORDER BY i.id;

id	itemname	Item Group	Category Type	Category
1	ABC-ORANGE	Fruits	Citrus Fruits	Oranges
2	DEF-ORANGE	Fruits	Citrus Fruits	Oranges
3	AB-Melons	Fruits	Melons	watermelons
4	MN-onion	vegetables		onion
5	PQ-potato	vegetables		potato

---

View on DB Fiddle