[quoted text, click to view] On 30 Nov 2004 20:26:25 -0800, Martin wrote:
>Dear Group
>
>I wonder whether you can push me in a direction on how to design the
>following statement. I'm looking for a SELECT with some tricky ORDER
>BY.
>
>The database table looks like this:
>
>MenuID TabText SubTabID TabOrderID
>------- ----------- ----------- -----------
>1 Main 0 1
>2 Cars 0 2
>3 Boats 0 3
>4 Planes 0 4
>5 Pick-Ups 2 1
>6 Campers 2 2
>
>The result should look like this:
>
>Main
>Cars
>Pick-Ups
>Campers
>Boats
>Planes
>
>Notice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' and
>must appear in the result directly following 'Cars'.
>
>In more detail:
>'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and
>'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabID
>value of an item identifies to what top-level category a subcategory
>belongs.
>The TabOrderID specifies in what order the items should be sorted,
>e.g. 'Pick-Ups' comes first and 'Campers' second.
>
>Thanks very much for your help & efforts!
>
>Martin
Hi Martin,
The following is untested, as you didn't post CREATE TABLE and INSERT
statements:
SELECT a.TabText
FROM MyTable AS a
LEFT JOIN MyTable AS b
ON b.MenuID = a.SubTabID
ORDER BY b.TabOrderID, a.TabOrderID
Note that this works if you have only one level of subcategories. If the
subcategory of Campers if divided further, my query will fail.
If you need a solution that works with more levels of subcategories, you
should consider using another table design. Your design (the adjacency
list model) is not particularly well suited for relational operations; the
nested sets model is often a better choice for modelling hierarchies.
If you google for "nested sets model Joe Celko", you should find several
explanatory messages on the nested sets model.
Best, Hugo
--
