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
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 categoryCategory
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.
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);