how i do this sql trick

Question

I have a table in database for categories and sub categories. Its internal structure is:

id int not null primary 
name text
subcatfrom int

it contains some of rows for categories and its sub categories. I want "SELECT" sql command to fetch categories and grouping their sub categories after it for every root category as following for example :

-cat1
--subcat1
--subcat2
-cat2
--subcat1
--subcat2

is it possible ?

http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql — Vikdor, Aug 23 '12 at 12:56
[MySql doesn't support recursive queries](http://stackoverflow.com/a/7631222/572644) — Daniel Hilgarth, Aug 23 '12 at 12:57
If you are coding in PHP, take a look at @Tim Copper answer http://stackoverflow.com/questions/5213309/php-recursive-menu-in-html-list-structure — FirmView, Aug 23 '12 at 13:02
How many levels can this hierarchy have? Are you only limited to 2? — Branko Dimitrijevic, Aug 23 '12 at 13:20

Muhammad Raheel · Answer 1 · 2012-08-23T13:21:49.523

That's very easy but with this structure

Table: category_id , name , parent_id

Sample Data

category_id    name   parent_id
   1           A        0
   2           B        0
   3           C        1
   4           D        1

This means A is a category which has 2 subcategories C and D. And parent_id 0 means it is a parent category Now the sql is

SELECT lc.category_id,
       lc.name,
       rc.subcategories
FROM categories
LEFT JOIN (
    SELECT  
        subcategory_id , 
        GROUP_CONCAT(name) AS subcategories  
    FROM categories)  AS rc 
ON lc.category_id = rc.parent_id

This will give you the following result

category_id    name      subcategories
   1           A          C,D
   2           B          (null)

score 1 · Answer 2 · answered Aug 23 '12 at 13:14

The original question wants the subcategories on separate rows. Here is one way:

select name
from ((select category as name, 1 as iscat, category as sortorder
       from t
      ) union all
      (select tsub.category as name 0 as iscat, t.category as sortorder
       from t join
            tsub on
            on t.subcategory_id = s.category_id
      )
     ) a
where not exists (select 1 from category c where c.subcategory_id = a.sortorder limit 1) 
order by sortorder, iscat desc, name

What is this doing? The inner union all is bringing together all categories and subcategories. It is assigning what you want in the table as well as information for sorting. The overall ordering is by "parent" category name.

The where clause is limiting this data to categories that are not the subcategory of anything else -- the top level categories.

score 1 · Answer 3 · answered Aug 23 '12 at 13:18

I am making the assumptions that- 1. you have just one level of parent child relationship. ie subcategory can't have further sub-category 2. For top level category, value of subcatfrom is 0

SELECT * FROM 
(
SELECT NAME AS parent_category_name, '' AS child_category_name FROM categories WHERE subcatfrom = 0
UNION
SELECT b.NAME AS parent_category_name, a.NAME AS child_category_name FROM categories a JOIN     categories b ON a.subcatfrom = b.id
) a ORDER BY a.parent_category_name, a.child_category_name;

how i do this sql trick

3 Answers3