-1

there is a table: Groups

It has three columns: ID, NAME, PARENT

This would flow in the following way.

  • Suppose there is a Group ELECTRONICS
  • Under ELECTRONICS, there is MOBILE
  • Under MOBILES there is SAMSUNG
  • Under SAMSUNG there is GALAXY EDGE
  • Under GALAXY EDGE there is 16GB and 8GB

the data in database would follow like:

ID             NAME                  PARENT
 1          ELECTRONICS               null
 2            MOBILE               ELECTRONICS
 3           SAMSUNG                 MOBILE
 4             16GB                  SAMSUNG
 5              8GB                  SAMSUNG

There may be N levels of hierarchy. I want to retrieve all the records of the last level. In this case, return 16GB and 8GB.

Imran Hemani
  • 599
  • 3
  • 12
  • 27
  • This is commonly solved with a recursive query using `START WITH` and `CONNECT BY`. This one looks simple enough to use `START WITH PARENT IS NULL CONNECT BY PRIOR NAME = PARENT`. – Bacon Bits Nov 22 '15 at 16:34
  • Here is **Sql Server** Version convert to **oracle** http://sqlfiddle.com/#!3/b7074/8 – Pரதீப் Nov 22 '15 at 16:40

1 Answers1

0

The usual approach to such a problem is a recursive query. In Oracle this can be done using connect by.

However: to get all rows from the last level no recursive query is necessary.

Those are all rows that do not appear in the parent column:

select *
from Groups
where name not in (select parent
                   from groups g2
                   where g2.parent is not null);

SQLFiddle: http://sqlfiddle.com/#!4/df70d/1

A recursive query can be used to find e.g. all nodes below a certain category, e.g. if you want to find everything below SAMSUNG:

select *
from groups
start with name = 'SAMSUNG'
connect by prior name = parent;