MySQL Forums
Forum List  »  Newbie

Re: Categories and Sub-headings
Posted by: Chris Stubben
Date: May 28, 2005 10:18PM

Hi,

Your solution is pretty good. You can combine those two queries using a UNION, add a path string, and order by the path.

create table foods (foodCategory varchar(12), foodItem varchar(12));

insert into foods values ('Cheese', 'Cheddar'),('Cheese', 'Cheshire'),('Cheese', 'Stilton'),('Cheese', 'Wensleydale'),('Fruit', 'Apple'),('Fruit', 'Melon'),('Fruit', 'Orange'),('Fruit', 'Pear' );


select DISTINCT concat('Category - ', foodCategory) as Foods, foodCategory as path from foods
UNION
select concat(repeat('-> ', 1), foodItem), concat(foodCategory, '.', foodItem) as path from foods
order by path;

+-------------------+--------------------+
| Foods | path |
+-------------------+--------------------+
| Category - Cheese | Cheese |
| -> Cheddar | Cheese.Cheddar |
| -> Cheshire | Cheese.Cheshire |
| -> Stilton | Cheese.Stilton |
| -> Wensleydale | Cheese.Wensleydale |
| Category - Fruit | Fruit |
| -> Apple | Fruit.Apple |
| -> Melon | Fruit.Melon |
| -> Orange | Fruit.Orange |
| -> Pear | Fruit.Pear |
+-------------------+--------------------+



Path delimited strings are a common way to model trees in sql, and will let you use any number of levels. Your table should look something like this (only one column for foods).


+-------------+-------+--------------------+
| Food | level | path |
+-------------+-------+--------------------+
| Cheese | 1 | Cheese |
| Cheddar | 2 | Cheese.Cheddar |
| Cheshire | 2 | Cheese.Cheshire |
| Stilton | 2 | Cheese.Stilton |
| Wensleydale | 2 | Cheese.Wensleydale |
| Fruit | 1 | Fruit |
| Apple | 2 | Fruit.Apple |
| Melon | 2 | Fruit.Melon |
| Orange | 2 | Fruit.Orange |
| Pear | 2 | Fruit.Pear |
+-------------+-------+--------------------+


Chris

Options: ReplyQuote


Subject
Written By
Posted
Re: Categories and Sub-headings
May 28, 2005 10:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.