Re: CONNECT BY - how to get leaves only

From: Kim Ng <kimmng_at_pebbles.uswnvg.com>
Date: 16 Dec 1994 18:23:15 GMT
Message-ID: <3csluj$nja_at_fred.uswnvg.com>

Hi everyone,

Mr. Chip Dawes suggested a very simple and elegant method of getting the "leaves" of the tree without even using "CONNECT BY". I am posting his solution in case somebody else is interested. (Read after "====" line)

Thank you very much, Chip.

---

 (Whatever I say and own are mine, mine and only mine! So, don't you dare   claim they are yours!)
 Kim Ng
 Paradigm Computer Consulting, Inc.
 20611 E Bothell-Everett Hwy SE, Suite 280  Bothell, WA 98012
 U.S.A.

---

From: chipd_at_mmsi.corp.mot.com (Chip Dawes)

Message-Id: <9412161743.AA01240_at_mmsi.corp.mot.com>
Content-Type: text
Content-Length: 1501

Apparently-To: kimmng_at_uswnvg.com
Status: R

The query I posted should still work. You would solve the same problem via a different technique. If your desired result is only the children and not the parents. You can find them 2 ways.

Technique 1)
Starting the root, decend each branch until you come to the terminating leaf, then print it.
  You choose correctly in trying to use the CONNECT BY statement to execute this alogithm.

Technique 2)
Find all the children who are not parents themselves. Avoid building the tree.

Using set operators, implementing Technique 2 is much simpler than Technique 1. Technique 1 is more proceedural in nature.

If I understand your table correctly, let me run though an example:

Table TEST_TABLE has column CHILD and PARENT. Some data looks like:

CHILD PARENT

  1     null
  2      1
  3      2
  4      3
  5     null
  6      5
  7     null
  8      7
  9     null
 10     null
 11     10
 12     11
 13     12

If we only want 4,6,8,9, and 13.
They are the leaves.
They are the childred who are not parents.

To select the children who are not parents, We'll first get all the children:

	SELECT CHILD
	FROM   TEST_TABLER

Next, we'll get all the parents:

	SELECT PARENT
	FROM   TEST_TABLE

Finally, we'll remove the parents from our list of children:

	SELECT CHILD
	FROM   TEST_TABLE

	MINUS

	SELECT PARENT
	FROM   TEST_TABLE

and voila! We get the desired result VERY quickly. Connect by always seems to run SLOW!, but using MINUS usually seems to run FAST.

Will it work for you?

Chip Received on Fri Dec 16 1994 - 19:23:15 CET