-2

I have this MySQL tabel and I'm trying to get the ids of the main category and each subcategory linked to it and the others linked to this subcategories... You can see an example below:

+----------------------+
| Catrgorias           |
+----------------------+
| id (int 11)          |
+----------------------+
| nombre (varchar 255) |
+----------------------+
| parent (int 11)      |
+----------------------+
+----+------------+--------+
| id | nombre     | parent |
+----+------------+--------+
| 1  | Cat1       | NULL   |
+----+------------+--------+
| 2  | Cat2.      | NULL   |
+----+------------+--------+
| 3  | SubCat1    | 1      |
+----+------------+--------+
| 4  | SubCat2    | 2      |
+----+------------+--------+
| 5  | SubSubCat1 | 3      |
+----+------------+--------+

Does anyone know how to get these ids in MySQL and get this result with something like this?

For categort_tree(1)

+----+     +-------+
| id |     | id    |
+----+  or +-------+
| 1  |     | 1,3,5 |
+----+     +-------+
| 3  |
+----+
| 5  |
+----+

For categort_tree(2)

+----+     +-----+
| id |     | id  |
+----+  or +-----+
| 2  |     | 2,4 |
+----+     +-----+
| 4  |
+----+
Pablo carrió
  • 31
  • 8
  • Umm -- `SELECT id FROM Catrgorias` will get you the first result you are looking for .. Unless I am missing something? .. – Zak Nov 26 '18 at 17:37
  • I've corrected the question to explain it better. I'm trying to get the main category id and the id of the categorias wich have it as parent and the subcategorias wich have as parent a category linket to the main etc. – Pablo carrió Nov 26 '18 at 20:55
  • you need to use a recursive cte to do this – Hogan Nov 26 '18 at 20:56

1 Answers1

0

You can do this with a recursive CTE

with cte as
(
   SELECT ID
   FROM CATEGORY 
   WHERE ID = %input

   UNION ALL

   SELECT ID
   FROM CATEGORY 
   JOIN CTE ON PARENT = CTE.ID
)
SELECT * FROM CTE
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • I can't do it becaus of the mariaDB version. The with statement was introduced in mariaDB 10.2.1 and my server is currently using version 10.0.37. There is another option? – Pablo carrió Nov 26 '18 at 21:24
  • @Pablocarrió -- nope. recursive is the only way. Unless you only want to go a specific number of levels deep. – Hogan Nov 27 '18 at 16:38