troubleshooting Question

SQL query question

Avatar of Ingo Foerster
Ingo Foerster asked on
MySQL ServerSQL
3 Comments1 Solution202 ViewsLast Modified:
Table definition:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

DROP TABLE IF EXISTS textitem
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
) engine=innodb;

Experimental rows table 1:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Experimental rows table 2:

INSERT INTO textitem VALUES
(1, 'mytext is mytext', 21);

Recursive Stored procedure:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Wrapper function for the stored procedure:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Select example:

SELECT id, name, getpath(id) AS path FROM category;

Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

how to expand the SQL to get the text from textitem with last ID?

+----+-----------+-----------------------------------------+--------------------------------+
| id | name      | path                                    | textiten.name                  |       
+----+-----------+-----------------------------------------+--------------------------------+
| 19 | category1 | category1                               |                                |
| 20 | category2 | category1/category2                     |                                |
| 21 | category3 | category1/category2/category3           |                                |
| 22 | category4 | category1/category2/category3/category4 |mytext is mytext                |
| 23 | categoryA | category1/categoryA                     |                                |
| 24 | categoryB | category1/categoryA/categoryB           |                                |
| 25 | categoryC | category1/categoryA/categoryC           |                                |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |                                |
+----+-----------+-----------------------------------------+--------------------------------+
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson
Database Administrator / Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros