CODEWITHSUNDEEP
phpmysqlselect
ipel
ipel

Reputation: 1348

mysql select categories e subcategories from the same table

I have the following table called "products":

id INT 11
product_name VARCHAR 255
category VARCHAR 255
subcategory VARCHAR 255
subsubcategory VARCHAR 255

I would like to read all categories, sub-categories and sub-sub-categories (only once each category) in one single query.

For now I have the following query, but im not sure it's working:

SELECT category, subcategory, subsubcategory FROM products WHERE category != '' GROUP by category, subcategory, subsubcategory ORDER by category ASC, subcategory ASC, subsubcategory ASC

The output should be something like:

clothes (main category)
clothes > pants (sub category)
clothes > pants > man (sub sub category)
clothes > pants > woman
clothes > skirt
jewelry
jewelry > necklace

Upvotes: 0

Views: 221

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You can do:

select category as rendered from products
union all 
select concat(category, ' > ', subcategory) from products
union all 
select concat(category, ' > ', subcategory, ' > ', subsubcategory) from products
order by rendered

Upvotes: 1

Related Questions