Interesting query

[stasJohn]

Hi, I'm curious if the follwoing can be done.

First, here's sample table data

| id | parent | ordering |
| 1  | 0      | 1        |
| 2  | 0      | 2        |
| 3  | 0      | 3        |
| 4  | 2      | 1        |
| 5  | 2      | 2        |

I want to perform a query which returns the ids like;

1
2
4
5
3

I'm using mysql 4.1

Any hints would be a great help. thanks in Advance.

 

[TonyGroves]

I don't follow the logic there. Can you explain?

 

[stasJohn]

The table is used to store a hierarchical menu structure.

For example:

Home
About
  About1
Tips
  Tips1
  Tips2

id | name      | parent | ordering |
------------------------------------
1  | Home      | 0      | 1        |
2  | About     | 0      | 2        |
3  | Tips      | 0      | 3        |
4  | About1    | 2      | 1        |
5  | Tips2     | 3      | 2        |
6  | Tips1     | 3      | 1        |

I want to query the db to return all menu items and have them listed in the order the menu items should be listed; Home, About, About1, Tips, Tips1, Tips2

I hope that makes better sense.


(I realize there may be a better way to set up the table, but this is the structure I'm stuck with)

 

[r937]

this will work exactly as requested on the sample provided --

order by
 case when parent=0 then id else parent end
,case when parent=0 then 0 else 1 end
,ordering

note that if your nav goes more than one level deep, it won't



r937.com | rudy.ca

 

[stasJohn]

Thanks r937 for the code.

The code works great when the "ids" are in order, but not if out of order. For instance, lets say "About" is deleted and the re-added;

id | name      | parent | ordering |
------------------------------------
1  | Home      | 0      | 1        |
3  | Tips      | 0      | 3        |
5  | Tips2     | 3      | 2        |
6  | Tips1     | 3      | 1        |
7  | About     | 0      | 2        |
8  | About1    | 7      | 1        |

The results from the sql will be

Home
Tips
  Tips1
  Tips2
About
  About1

"About" should actually preceed Tips

 

[r937]

"About" should actually preceed Tips

why?

r937.com | rudy.ca

 

[r937]

okay, try this --

select t1.id
     , t1.parent
     , t1.ordering
     , t1.name
     , t1.ordering as sortkey1
     , 0           as sortkey2
  from stasJohn as t1
 where t1.parent = 0  
union all
select t2.id
     , t2.parent
     , t2.ordering
     , t2.name
     , t1.ordering as sortkey1
     , t2.ordering as sortkey2
  from stasJohn as t1
inner
  join stasJohn as t2
    on t2.parent = t1.id
 where t1.parent = 0  
order
    by sortkey1
     , sortkey2

results:

id parent ordering name sortkey1 sortkey2
1  0  1  Home    1  0
7  0  2  About   2  0
8  7  1  About1  2  1
3  0  3  Tips    3  0
6  3  1  Tips1   3  1
5  3  2  Tips2   3  2

r937.com | rudy.ca

 

[stasJohn]

Nice, that works fantastic. I appreciate it.

 

[JGH]

Glad this topic came up.... Is there a single query that be done for a table designed like a linked list, where ordering is dictated only by the parent:

id | name      | parent |
-------------------------
1  | First     |   0    |
2  | Second    |   1    |
3  | Fourth    |   5    |
4  | Fifth     |   3    |
5  | Third     |   2    |
6  | Sixth     |   4    |

Thanks - Jim

 

[r937]

for your sample, jim, yes, but it requires a 6-way self-join

r937.com | rudy.ca

 

[JGH]

But I take it there is no single query that will do this for lists of variable size?

Thanks - Jim

 

[r937]

that would require recursive sql, which, as far as i know, mysql does not support

r937.com | rudy.ca