Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Angry Unanswered: Select Leaf nodes of trunk (JOIN or ??)

    SQL Gurus please help.

    I have following table:

    +-------------+----------------------+--------+
    | category_id | name | parent |
    +-------------+----------------------+--------+
    | 1 | ELECTRONICS | NULL |
    | 2 | TELEVISIONS | 1 |
    | 3 | TUBE | 2 |
    | 4 | LCD | 2 |
    | 5 | PLASMA | 2 |
    | 6 | PORTABLE ELECTRONICS | 1 |
    | 7 | MP3 PLAYERS | 6 |
    | 8 | FLASH | 7 |
    | 9 | CD PLAYERS | 6 |
    | 10 | Single CD | 9 |
    | 11 | 3 CD Changer | 9 |
    | 12 | 5 CD Changer | 9 |
    | 13 | 2 WAY RADIOS | 6 |
    +-------------+----------------------+--------+


    I want to retrieve the leaf nodes of a trunk from this data. I know to retrieve all the leaf nodes I can write a query i.e

    SELECT t1.name FROM
    category AS t1 LEFT JOIN category as t2
    ON t1.category_id = t2.parent
    WHERE t2.category_id IS NULL;


    But I am interested to get the leaf nodes of a particular branch/trunk node e.g Portable Electronics

    Thanks in advance. I hope prompt reply

    WAK

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I think it's only posible if you know how many levels you have in your structure. Otherwise you need some kind of recursive
    what I think is not possible do in one select statement - loop in PL/SQL or Transact SQL could solve that.
    For your scenario (2 levels) you can use this statement:
    Code:
    select name 
    from category c1
    where not exists (select 1 from category c2 where c2.parent = c1.category_id)
    and parent in (select category_id from category c3 where c3.name = 'PORTABLE ELECTRONICS' -- first level
    		union all
    		select category_id from category c4 where c4.parent = (select category_id from category c3 where c3.name = 'PORTABLE ELECTRONICS')) -- second level
    name
    ------------
    FLASH
    Single CD
    3 CD Changer
    5 CD Changer
    2 WAY RADIOS

Posting Permissions

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