CODEWITHSUNDEEP
mysqlsqlrecursion
alexandernst
alexandernst

Reputation: 15109

Recursively select all subcategories of a category

I have a table with the following fields:

node_id (int, AI)
category_id, (int)
parent_node_id (int)

How can I select all the nodes (or categories, if you wish) that hang down from a given category id. And by "hang down" I mean all the recursively stored nodes.

Example:

Category  node  parent 
       1     1  none
       2     2  none
       3     3  none

       4     4       1
       5     5       4
       6     6       5

Expected output of the select:

Category  node  parent 
       1     1  none
       4     4       1
       5     5       4
       6     6       5

Upvotes: 0

Views: 1190

Answers (3)

Uber Bot
Uber Bot

Reputation: 511

You can create a function that will return whether the category is a child at any level to category you are interested.

CREATE FUNCTION `is_child_of`(id INT, related_to_id INT) RETURNS int(11)
BEGIN
    DECLARE `exists` BOOL;
    /* to avoid infinite loop */
    SELECT EXISTS(SELECT `parent_id` FROM `category` WHERE `category_id` = id) INTO `exists`;
    IF `exists` IS FALSE THEN
        RETURN 0;
    END IF;

    WHILE id IS NOT NULL DO
        IF id = related_to_id THEN
            RETURN 1;
        END IF;

        SELECT `parent_id` INTO id FROM `category` WHERE `category_id` = id;
    END WHILE;
    RETURN 0;
END

Then just select by it's result regarding category you want to drill down.

For example for a category with id - 1

SELECT * FROM `category` WHERE `is_child_of`(category_id, 1);

I admit it is far from being efficient. It is difficult to be efficient when dealing with hierarchy in a relational database.

Upvotes: 1

mohan111
mohan111

Reputation: 8865

exactly I don't know what you are looking for but as per my assumption.

DECLARE @Table1  TABLE 
    (node_id varchar(9), category_id varchar(5), parent_node_id varchar(11))
;

INSERT INTO @Table1
    (node_id, category_id, parent_node_id)
VALUES
    ('Category1', 'node1', 'parentnone.'),
    ('Category2', 'node2', 'parentnone.'),
    ('Category3', 'node3', 'parentnone.'),
    ('Category4', 'node4', 'parent1.'),
    ('Category5', 'node5', 'parent4.'),
    ('Category6', 'node6', 'parent5.')
;
select node_id, category_id, parent_node_id from (
select node_id, category_id, parent_node_id,Row_number()OVER(PARTITION BY parent_node_id ORDER BY node_id desc)RN from @Table1
GROUP BY node_id, category_id, parent_node_id
)T
WHERE T.RN = 1
--ORDER BY cat desc
ORDER BY  RIGHT(category_id,1)

Upvotes: 1

Chris Lear
Chris Lear

Reputation: 6742

Assuming the table is called categories:

select
    children.category_id as category,
    children.node_id as node,
    parent.node_id as parent_node_id
from categories parent 
join categories children
on parent.node_id = children.parent_id;

That should get you somewhere.

Upvotes: 0

Related Questions