Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: parent child, display as follows - possible with SQL?

    Hi there, I would like to accomplish the following, I have a table as follows:

    cat_id
    parent_id
    cat_descr


    ------------------
    Table filled as:
    1, 0, Root
    2, 1, Computer
    3, 2, Harddisk
    4, 2, Floppy drive

    I would like to get a display as of:

    Root
    Root > Computer
    Root > Computer > Harddisk
    Root > Computer > Floppy drive

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it's possible

    you need three queries in a union
    PHP Code:
    select l1.cat_descr as category1
         
    '>'
         
    l2.cat_descr as category2
         
    '>'
         
    l3.cat_descr as category3   
      from yourtable l1
    inner
      join yourtable l2
        on l1
    .cat_id l2.parent_id          
    inner
      join yourtable l3
        on l2
    .cat_id l3.parent_id     
    union all    
    select l1
    .cat_descr
         
    '>'
         
    l2.cat_descr
         
    ' '
         
    ' ' 
      
    from yourtable l1
    inner
      join yourtable l2
        on l1
    .cat_id l2.parent_id          
    union all    
    select l1
    .cat_descr
         
    ' '
         
    ' '
         
    ' '
         
    ' ' 
      
    from yourtable l1
    order by 1
    ,2,3,4,
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2002
    Posts
    134
    In general you have to use recursion (Oracle "connect by", DB2/MS SQL Server Yukon - "with")

    DB2 example (I added a limit by level, remove if not needed):

    with
    yourtable(cat_id, parent_id, cat_descr) as (values (1, 0, 'Root'), (2, 1, 'Computer'), (3, 2, 'Harddisk'), (4, 2, 'Floppy drive'))
    , tree(level, id, value) as
    (
    select 1, cat_id, cast(cat_descr as varchar(100)) from yourtable where parent_id = 0
    union all
    select t2.level+1, t1.cat_id, t2.value || '>' || t1.cat_descr
    from
    yourtable t1, tree t2
    where t1.parent_id = t2.id
    and t2.level < 10
    )
    select value
    from tree
    order by level



    OUTPUT:

    VALUE
    ---------------------------
    Root
    Root>Computer
    Root>Computer>Harddisk
    Root>Computer>Floppy drive

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •