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