PostgreSQL: Select material with all its parent categories

Question

I have two tables

Categories

• Id
• Name
• ParentCategoryId (reference to Categories table column Id)

Materials

• Id
• Name
• CategoryId (reference to Categories table column Id)

I want to select specified material and all its parent categories

Categories table data

 Id  Name                 ParentCategoryId
------------------------------------------
  1  food                 null
  2  fruits               1
  3  exotic fruits        2
  4  IT equipment         null

Materials table data

 Id Name             CategoryId
 -------------------------------
  1  mango            3

When I select mango which is Id=1, I want to get in my result set all related categories of this material (food, fruits, exotic fruits)

I wrote something like this by using recursive with clause

WITH RECURSIVE RecursiveCTE AS
(
    SELECT m."Id", m."Name", c."Id" as "CategoryId", c."Name" "CategoryName"
    FROM "Materials" m
    INNER JOIN "Categories" c on c."Id" = m."CategoryId"
    WHERE m."Id" = 1

    UNION ALL

    SELECT rt."Id", rt."Name", c."Id" as "CategoryId", c."Name" "CategoryName"
    FROM RecursiveCTE rt
    INNER JOIN "Categories" c ON c."ParentCategoryId" = rt."Id"
)
SELECT * FROM RecursiveCTE

Answer 1

resolved )))

WITH RECURSIVE RecursiveCTE  AS(
    SELECT m."Id", m."Name", c."Id" as "CategoryId", c."Name" as "CategoryName", c."ParentCategoryId"
    FROM "Materials" m
    INNER JOIN "Categories" c on c."Id" = m."CategoryId"
    where m."Id" = 1

    union all

    SELECT rt."Id", rt."Name", c."Id" as "CategoryId", c."Name" as "CategoryName", c."ParentCategoryId"
    FROM RecursiveCTE rt
    INNER JOIN "Categories" c on c."Id" = rt."ParentCategoryId"
    WHERE rt."ParentCategoryId" is not null
)
SELECT * FROM RecursiveCTE