Ordering Query

[Naoise]

I have a table which has Categories and SubCategories which themselves can have subcategories so you can have

Cars -> Ford but also
Fruit -> Apple -> Granny Smith

So given a table like this

tblFoo
ID | ParentID | Position | Category
1 0 1 Fruit
2 0 2 Cars
3 1 1 Apple
4 1 2 Orange
5 1 3 Lemon
6 2 1 Ford
7 2 2 Nissan
8 2 3 Honda
9 3 1 Granny Smith


I would like to have a query to produce


ID | ParentID | Position | Category
1 0 1 Fruit
3 1 1 Apple
9 3 1 Granny Smith
4 1 2 Orange
5 1 3 Lemon
2 0 2 Cars
6 2 1 Ford
7 2 2 Nissan
8 2 3 Honda


Any help appreciated, thanks

 

[fredericofonseca]

you need to have another field (either on this table on on a related table) that can tell you how to group your categories.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Juice05]

How many sub categories can there be?

 

[Naoise]

There can be an unlimited amout of subcategories, but in reality it will not go further than 3 subcategories deep.

Fruit -> Apple -> Granny Smith -> African Granny Smith

I would like to steer clear of cursors if possible to do in a single SQL query.

 

[Juice05]

You should rethink your design. If you had a nodelevel or something like that it may make it easier.

 

[r937]

the design is fine, no additional columns are needed

however, in order to obtain the correct sequence, you need to return 3 additional sort columns:

select level_1.Position as sortkey1
     , level_2.Position as sortkey2
     , level_3.Position as sortkey3
     , level_3.ID
     , level_3.ParentID
     , level_3.Position
     , level_3.Category
  from tblFoo as level_1
inner
  join tblFoo as level_2
    on level_2.ParentID = level_1.ID  
inner
  join tblFoo as level_3
    on level_3.ParentID = level_2.ID  
 where level_1.ParentID = 0    
union all    
select level_1.Position as sortkey1
     , level_2.Position as sortkey2
     , null             as sortkey3
     , level_2.ID
     , level_2.ParentID
     , level_2.Position
     , level_2.Category
  from tblFoo as level_1
inner
  join tblFoo as level_2
    on level_2.ParentID = level_1.ID  
 where level_1.ParentID = 0    
union all    
select level_1.Position as sortkey1
     , null             as sortkey2
     , null             as sortkey3
     , level_1.ID
     , level_1.ParentID
     , level_1.Position
     , level_1.Category
  from tblFoo as level_1
 where level_1.ParentID = 0      
order
    by sortkey1
     , sortkey2
     , sortkey3

this query produces the following results:

1 NULL NULL  1 0 1 Fruit
1   1  NULL  3 1 1 Apple
1   1    1   9 3 1 Granny Smith
1   2  NULL  4 1 2 Orange
1   3  NULL  5 1 3 Lemon
2 NULL NULL  2 0 2 Cars
2   1  NULL  6 2 1 Ford
2   2  NULL  7 2 2 Nissan
2   3  NULL  8 2 3 Honda

sweet, eh?


r937.com | rudy.ca

 

[Juice05]

So this will work if he adds a child node under Granny Smith and then another one under that?

 

[r937]

juice05, no, the pattern would have to be extended with another couple of queries in the union with additional joins, wouldn't it

i never promised it would be simple and elegant, but it does work, and it's a whole lot better than what most people usually do in this instance -- write a query inside a loop and build arrays



r937.com | rudy.ca

 

[Juice05]

I agree, your query does work.

However he mentioned that the possibility to have more children nodes was infinite. That is why I recommended a design change.

If your application allows a user to do something, you can rest assured that it will happen. Never fails.

 

[r937]

you are right, if there were infinite levels

however, i based my reply on this:

in reality it will not go further than 3 subcategories deep.

Fruit -> Apple -> Granny Smith -> African Granny Smith

and to be honest i really should've gone to the 4th level (my query stopped at the 3rd)


the reality of setting a maximum number of levels is nowhere more important than a web site navigation scheme -- even if you have seventeen levels, you typically don't want to go down more than two or three from wherever you are

in particular, you never see dropdowns on a home page that go more than 3 or 4 deep (and that's already too far, in my opinion)

in reality this happens more often than "infinite" levels

in my opinion it would be a good thing to restrict what the users can do, but i do grant that this is subjective and must be considered in context of the application

r937.com | rudy.ca