Is this an academic exercise or is it a a real life problem - if its a real life problem can you expand on the problem and hopefully there will be a limit to the hierarchy.
If there is a limitless hierarchy then I'm not sure you can write a query in mySQL (in oracle you can use the CONNECT BY syntax - but this is not supported by mySQL) I think you would need to write procedure to extract the data.
If there is a limit to the hierarchy you can join the table to itself multiple times, the number of level of hierarchy to be exact. Assume the table is call my_big_category_table and the examples are oracle syntax not mySQL
1 level of hierarchy (ie none)
Code:
select lev0.Serial_no,
lev0.Category_Name,
lev0.Parent_Number
from my_big_category_table lev0 /* alias for the table is lev0 */
where lev0.Parent_Number is null
2 levels of hierarchy
Code:
select lev0.Serial_no,
lev0.Category_Name,
lev0.Parent_Number,
lev1.Category_Name,
lev1.Parent_Number
from my_big_category_table lev0, /* alias for the table is lev0 */
my_big_category_table lev1 /* alias for the table is lev1 */
where lev0.Parent_Number is null
and lev0.Serial_no = lev1.Parent_Number (+) /* this is the outer join syntax in oracle */
3 levels of hierarchy
Code:
select lev0.Serial_no,
lev0.Category_Name,
lev0.Parent_Number,
lev1.Category_Name,
lev1.Parent_Number,
lev2.Category_Name,
lev2.Parent_Number
from my_big_category_table lev0, /* alias for the table is lev0 */
my_big_category_table lev1, /* alias for the table is lev1 */
my_big_category_table lev2 /* alias for the table is lev2 */
where lev0.Parent_Number is null
and lev0.Serial_no = lev1.Parent_Number (+) /* this is the outer join syntax in oracle */
and lev1.Serial_no = lev2.Parent_Number (+) /* this is the outer join syntax in oracle */
and so on ... not so great if there are lots of levels of hierarchy