Having a design table like this one:
create table tbl_categories (
category_id int auto_increment,
parent_category_id int, -- internal foreign key
category_name varchar(255)
);
Let's add some records:
insert into tbl_categories (parent_category_id, category_name) values (null,'category 1'),(1, 'sub category 1'),(2, 'sub sub category 1'),(1, 'sub category 2'),(1, 'sub category 3');
How could I select to return as this:
category 1
category 1 >> sub category 1
category 1 >> sub category 1 >> sub sub category 1
category 1 >> sub category 2
category 1 >> sub category 3
just wondering if it there is any pattern to archive this on a single query.
Thank you.
