Re: Categories and Sub-headings
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