Recursive query for postgresSQL parent/child

Question

Asking for a little help on a recursive query syntax, and of course result.

As you will see I have a table with category parent and child ( a parent can have infinite children). Querying the category dictionary (linked to a real category)

And I want to return only the last child of every category tree

Updated my code, and information

EDIT

WITH RECURSIVE cat(id) AS (
    SELECT
        *
    FROM
        category_dictionary
            LEFT JOIN category_dictionary.category ON category.id
    WHERE
        category.parent is NOT NULL
    UNION
    SELECT
        *
    FROM
        cat
            LEFT JOIN cat.category ON category.id
    WHERE
        category.parent is NOT NULL
)
SELECT
    *
FROM
    cat

Table information:

Category_dictionary is a table the join category on parameter category
Category is the main table with Parent entry.

Sample data:

category_dictionary entry:

ID : name (Men) : category_id

category entries:

category_id : name : parent (null or category_id)

As a result I want all the last child of each category entries, I mean the category that doesn't have child.

"*last child*" implies an ordering. What do you want to order the children by? — Bergi, Oct 01 '19 at 17:08

score 1 · Accepted Answer · answered Sep 17 '19 at 11:56

A recursive query is not needed to find the deepest children. Instead, one would look at entries that are not a parent (so no other child exists). Such entries ID is not included in the parent column.

You can then join this categories to other tables

SELECT *
FROM category cat
  JOIN category_dictionary cat_dic ON cat.id = cat_dic.id
WHERE NOT EXISTS
 (SELECT 1 FROM category cat2
  WHERE cat2.parent = cat.id);

Recursive query for postgresSQL parent/child

EDIT

1 Answers1