Link to home
Start Free TrialLog in
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

ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Ingo Foerster
Ingo Foerster

ASKER

Thank you very much for this solution.