CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
Jason4Ever
Jason4Ever

Reputation: 1479

how i do this sql trick

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 ?

Upvotes: 2

Views: 202

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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)

Upvotes: 1

piyush
piyush

Reputation: 976

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 1

Related Questions