Self join tree MYSQL

Question

Here is my categories table

category_id | parent_id
 ____________________________
       27   |       25
       28   |       27
       29   |       25
       26   |       28
       25   |        0
       30   |        0
       31   |       26
    ..........
    ..........

I want to show the records like this.

   category_id  | parent_id
 ____________________________
    25  |        0     
    27  |       25
    28  |       27
    26  |       28
    31  |       26
    29  |       25
    30  |        0

    ..........
    ..........

I have seen lots of post on it but I find nothing for me. Please let me know query for my categories table. Thanks. Here are some links that i have looked already. 1: enter link description here 2: enter link description here

`27` and `29` have the same `parent_id 25`, why `29` is after `31`? — Blank, Jun 05 '17 at 08:26
It looks like you only want to order the result in a different order? There is no difference in the two results above except the order? But I cant figure out what order that is...? Are there more columns in the table? — Sourcery, Jun 05 '17 at 08:57

score 1 · Accepted Answer · edited Jun 05 '17 at 15:34

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(category_id INT NOT NULL
,parent_id INT NULL
);

INSERT INTO my_table VALUES
(27,25),
(28,27),
(29,25),
(26,28),
(25,NULL),
(30,NULL),
(31,26);

SELECT DISTINCT t1.*
  FROM my_table t1
  LEFT 
  JOIN my_table t2 
    ON t2.parent_id = t1.category_id
  LEFT 
  JOIN my_table t3 
    ON t3.parent_id = t2.category_id
  LEFT 
  JOIN my_table t4 
    ON t4.parent_id = t3.category_id
 ORDER
    BY t4.parent_id DESC 
     , t3.parent_id DESC
     , t2.parent_id DESC
     , t1.parent_id DESC;

+-------------+-----------+
| category_id | parent_id |
+-------------+-----------+
|          25 |      NULL |
|          27 |        25 |
|          28 |        27 |
|          26 |        28 |
|          31 |        26 |
|          29 |        25 |
|          30 |      NULL |
+-------------+-----------+

or something like that.

Because MySQL does not yet have native support for recursion^†, this becomes a bit of mouthful, hence the popularity of alternative models, e.g. nested set...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(category_id INT NOT NULL
,lft INT NOT NULL
,rgt INT NOT NULL
);

INSERT INTO my_table VALUES
(0,1,16),
(25,2,13),
(27,3,10),
(28,4,9),
(26,5,8),
(31,6,7),
(29,11,12),
(30,14,15);

SELECT * FROM my_table ORDER BY lft;
+-------------+-----+-----+
| category_id | lft | rgt |
+-------------+-----+-----+
|           0 |   1 |  16 |
|          25 |   2 |  13 |
|          27 |   3 |  10 |
|          28 |   4 |   9 |
|          26 |   5 |   8 |
|          31 |   6 |   7 |
|          29 |  11 |  12 |
|          30 |  14 |  15 |
+-------------+-----+-----+

^† MySQL 8.0 will add support for recursive CTE syntax.

@BillKarwin - Oh man, now I've got to go learn a whole load of new stuff! — Strawberry, Jun 05 '17 at 16:27

Self join tree MYSQL

1 Answers1