-2

I have a category table with three columns.

 categories: id, name, parent_id

How can I print these categories in tree view using SQL command?

Sample:

 Men
      Top
           Shirts
           Tshirts
      Bottom
           Jeans
 Women
      Top
           Jackets
           Tshirts
      Bottom
           Jeans
Accessories
      Belt
      Cap
EfendiDev
  • 47
  • 2
  • 11
  • recommended reading: [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree), [Find highest level of a hierarchical field: with vs without CTEs](http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes) – RandomSeed Jan 29 '15 at 11:10
  • 1
    Typically, issues of data display would be handled in a presentation layer. – Strawberry Jan 29 '15 at 11:35

1 Answers1

0

You probably need to do this using recursion, for example something like this stored procedure...

DELIMITER //
CREATE PROCEDURE PrintHierachy
(IN parentId DECIMAL(19, 0), IN indent VARCHAR(1000))
BEGIN
    DECLARE itemName VARCHAR(255);
    DECLARE childId DECIMAL(19, 0);
    DECLARE csr CURSOR FOR 
        SELECT id 
        FROM MY_TABLE 
        WHERE parentId = parentId 
        ORDER BY NAME;

    IF parentId > 0 THEN
        SELECT itemName INTO FROM MY_TABLE WHERE id=parentId;
        % Print it
        SELECT concat(indent, itemName);
    END IF;

    OPEN csr;

    child_loop: LOOP
        FETCH csr INTO childId;

        IF done THEN
            LEAVE child_loop;
        END IF;
        % Recurse
        CALL PrintHierachy(concat('    '), childId);
    END LOOP;

    CLOSE csr;
END;

This assumes that the top level has parent Ids of "0".

e.g. CALL PrintHierachy('', 0)

(I've not used stored procs in MySQL before, but have in other DBMSs so forgive me if this does not compile!)

BretC
  • 4,141
  • 13
  • 22
  • ^ This will only order things alphabetically! If you want "Men" to appear above "Women" and "Accessories", you may need a table or another column that stores this ordering... – BretC Jan 29 '15 at 11:35
  • And I agree with the comment above that things like this should be done in the presentation layer!! – BretC Jan 29 '15 at 11:36