[SOLVED] Return multiple levels from same table

[NargsBrood]

I have a groups table that sort of has this structure:

 

group_id PK

parent_group_id FK points to group_id of same group table

group_name

ect...

 

The parent_group_id gives ownership of a group to another group and this can continue down several levels.

 

Now - Group_D has parent id pointing to Group_C who has a parent id pointing to Group_B who has parent id pointing to Group_A

 

If an object belongs to Group_D then it also belongs to Group_C and so belongs to B and so belongs to group A. 

 

What would be the most efficient way to return all of the groups that an object belongs to?

 

So - an Object belongs to Group D and so i want to return D, C, B, and A.

 

i've tried

 

select * from groups 
where group_id = [Group_D's group_id]
or group_id = (
                select parent_id from groups where group_id = [Group_D's group_id]
              )
or group_id = (
                select parent_id from groups where group_id = 
                  ( 
                    select parent_id from groups where group_id = [Group_D's group_id] 
                  )
              )
or group_id = (
                select parent_id from groups where group_id = 
                  (
                    select parent_id from groups where group_id = 
                      ( 
                        select parent_id from groups where group_id = [Group_D's group_id] 
                      ) 
                  )
              )

I can keep adding nested selects for each level I want to go up the chain but MySQL has an issue with this

when i get to the 4th or 5th level... it errors out.

 

Is there a better, more efficient way to do this that doesnt use all of the nested selects that wouldnt be too much for MySQL to handle?

[mjahkoh]

Try This

Assuming you have month,finyear,id in a table named calendar

select month,finyear,id,

CASE month when "01" then "January"

when "02" then "February"

when "11" then "November"

when "12" then "December"

END

from calendar where year = "2005" order by month

 

[NargsBrood]

are you sure this reply was for this thread?  i dont understand how it applies in the least

[NargsBrood]

http://forums.devshed.com/mysql-help-4/returning-multiple-levels-from-same-table-592263.html#post2211788

 

resolved