MySQL query: multi level category on the same table

Question

Good day earthlings,

This is my table structure

acctCode| refAcctCode | acctName
------    ----------    --------
 AC001                    SALES
 AC002                    AR
 AC003     AC001          FURNITURE
 AC004     AC003          OFFICE FURNITURE
 AC005     AC002          INVOICE
 AC006     AC001          UTILITIES

and expected output would be

SALES
    FURNITURE
        OFFICE FURNITURE
    UTILITIES
AR
    INVOICE

however it its not only limited on level 3 alone but to the nth

eg:

Parent1
  Child1
    child1.1
      child1.1.1
        child1.1.1.1
  child2
  child3
  child3

parent2
  child1
  child2
  child3

Given the table structure format, how do I query to get that output?

thanks

Sorry sir but my current connection is not stable ATM. Page load longer than it was expected. — Anvy, Jul 27 '17 at 15:00
MySQL doesn't presently have native support for recursion. Options include joining the table to itself as often as could be required, writing a sproc, handling the recursion logic in application code, or switching to an alternativemodel, e.g. nested sets. All of these options have been widely discussed. — Strawberry, Jul 27 '17 at 15:04
I see, can you provide me an example, or it is possible to limit in level 3, I am new to query and start to learning myself. — Anvy, Jul 27 '17 at 15:09

Carsten Massmann · Answer 1 · 2017-07-27T15:56:29.980

Here is a solution based on a user defined procedure:

CREATE PROCEDURE list()
BEGIN
 DROP TABLE IF EXISTS tmp; -- for collecting the records
 CREATE TABLE tmp (id varchar(1024), name varchar(128));
 SET @i=-5; SET @s=0;      -- @i: offset for id comparison
                           -- @s: space padding for name column
 INSERT INTO tmp           -- fill in entries without refAcctCode
 SELECT a.acctCode, a.acctName FROM tbl a WHERE a.refAcctCode is null;

                           -- do as often as necessary ...    
 WHILE ROW_COUNT() > 0 DO 
  SET @i=@i+6; SET @s=@s+2;-- increment variables
  INSERT INTO tmp
  SELECT CONCAT(id,' ',a.acctCode), CONCAT(SPACE(@s),a.acctName) 
  FROM tbl a INNER JOIN tmp b ON SUBSTR(id,@i)=a.refAcctCode;
 END WHILE;

 SELECT * FROM tmp ORDER BY id;  -- list the consolidated temp. table
END;

By executing it with

CALL list()

You get the following output:

    id                name
------------------------------------------
1   AC001             SALES
2   AC001 AC003         FURNITURE
3   AC001 AC003 AC004     OFFICE FURNITURE
4   AC001 AC006         UTILITIES
5   AC002             AR
6   AC002 AC005         INVOICE

You can find a working Demo here: http://rextester.com/ZRXN57872

I used spaces to prefix the accName column. This makes it easier to see the category levels in the results listing. Feel free to play around with the procedure to make it fit your particular needs.

splendid sir. however when I paste code on mysql. I got errors on while statement. — Anvy, Jul 28 '17 at 06:33
Can you provide us with the actual messages? Sometimes with MySQL there can be issues with delimiters, see here: https://stackoverflow.com/questions/10259504/delimiters-in-mysql — Carsten Massmann, Jul 28 '17 at 07:24

MySQL query: multi level category on the same table

1 Answers1