Retrieve a complete list of all Categories, Sub Categories, Sub Sub Categories, etc. in only one SQL query

Question

I have following table structure and data in MySQL

CatID  CatName     ParentCatID
-------------------------------
1       Shirts        NULL
2       Short Sleev    1
3       Long Sleev     1
4       Collarless     2
5       Collar         2
6       Collarless     3
7       Collar         3
8       Square Cut     4
9       Round Cut      4
10      Square Cut     6
11      Round Cut      6

Return data that I want is something like this:

Shirts > Short Sleev
Shirts > Long Sleev
Shirts > Short Sleev > Collarless
Shirts > Short Sleev > Collar
Shirts > Long Sleev > Collarless
Shirts > Long Sleev > Collar
Shirts > Short Sleev > Collarless > Square Cut
Shirts > Short Sleev > Collarless > Round Cut
Shirts > Short Sleev > Collar > Square Cut
Shirts > Short Sleev > Collar > Round Cut

How can we get these data using one single SQL query in MySQL?

Answer 1

Check out this and this article.

Answer 2

I do not think MySQL has support for tree-like structures (as opposite to Oracle, for instance).

If you know the maximum depth of your tree, you can do something with joins... If you don't, it'll be harder.

You can take a look at this article, for instance, about hierarchical data in MySQL.

If you only have a few lines in your table, a potentially good solution would be to just "select *", and to manipulate all this in PHP.