0

I have a table (models) that I want to loop through. It have a column called category, linked to another table (categories).

id  name     parent
1   Audio    NULL
2   Video    NULL
3   Monitor  2
4   Monitor  1
5   Light    NULL
6   HD       3
7   4K       4

with the sql below I get the name of the level but I would like to get the whole path to be able to sort based on the full category path. For example id 7 now gives me "4K" but I would like to get "Video / Monitor / 4K", id 4-> "Audio / Monitor". As you see there can be sub categories with the same name. 

SELECT 
m.id AS Id,
c.name AS Category,
m.model AS Model 
FROM models m
LEFT JOIN categories c ON m.category=c.id
GROUP BY  m.id
ORDER BY Category

For the moment I have a php loop that starts with the id in the models table, then checks if the parent is NULL, makes a new sql and so on until parent is NULL. There must be a better way to do it?

UPDATE: The server is running MySQL 5.6 and it can not be updated for now
UPDATE 2: I saw the suggested duplicate before but in my case I know the id of the child and I want to find all parents.

nickehallgren
  • 85
  • 6
  • According to the data set your `Monitor` has parent `id =1` which is `Audio` not `Video` ;-) – Alex Feb 15 '19 at 22:38
  • 1
    Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – Alex Feb 15 '19 at 22:39
  • @Alex I updated the question, and I have two sub categories called `Monitor` one with parent 1 and one with parent 2 – nickehallgren Feb 16 '19 at 07:00

0 Answers0