Select Leaf nodes of trunk (JOIN or ??)

wajid · #1 (**permalink**) 12-09-05, 00:31

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

madafaka · #2 (**permalink**) 12-09-05, 05:18

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

Internet Services

Web Development

Digital Marketing

Consumer Tech