Parent / Child heirachy in MySql

Discussion in 'MySQL' started by blackbug, Aug 24, 2006.

0
  1. #1
    Has anyone done anything along these lines

    
SELECT 	*
FROM	thingy
CONNECT BY 
PRIOR	thingy.id = thingy.parent_id
START WITH thingy.id = ?;
    Code (markup):
    But in mysql? It works in Oracle, but I'll be damned if I can manage it in mysql, and I don't want to do it all in the application layer (php or perl).

    Get what I mean?

    So for this table

    id parent_id
    0 NULL
    1 0
    2 0
    3 2
    4 3
    5 3
    6 5

    
SELECT 	*
FROM	thingy
CONNECT BY 
PRIOR	thingy.id = thingy.parent_id
START WITH thingy.id = 3;
    Code (markup):
    ...would return

    id parent_id
    3 2
    4 3
    5 3
    6 5


    Cheers!
     
    blackbug, Aug 24, 2006 IP
  2. blackbug

    blackbug Peon

    Messages:
    1,002
    Likes Received:
    89
    Best Answers:
    0
    Trophy Points:
    0
    #2
    And no, this isn't good enough: :)

    select * from thingy where parent_id = 1
union
select * from thingy where parent_id in (select id from thingy where parent_id = 1)
union
select * from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id = 1))
union
select * from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id = 1)))
union
select * from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id in (select id from thingy where parent_id = 1))))
    Code (markup):
    Although it is what I'm doing at the moment...
     
    blackbug, Aug 24, 2006 IP