Avatar of Ingo Foerster
Ingo Foerster
 asked on

SQL query question

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;

Open in new window


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

Open in new window


Experimental rows table 2:

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

Open in new window


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 ;

Open in new window


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 ;

Open in new window


Select example:

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

Open in new window


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 |
+----+-----------+-----------------------------------------+

Open in new window


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 |                                |
+----+-----------+-----------------------------------------+--------------------------------+

Open in new window

MySQL ServerSQL

Avatar of undefined
Last Comment
Ingo Foerster

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

SELECT c.id, c.name, getpath(c.id) AS path,  t.name
FROM category c
left outer join  textitem t on c.id = t.parent_id

no points please, just a tiny re-arrangement of the previous query
Ingo Foerster

ASKER
Thank you very much for this solution.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes