Re: An SQL Teaser Question
Steve Romankiw1 wrote:
>
> I would like to generate an Org Chart using SQL. I am able to generate a
> list when working from the root.
> However, I have hit a mental block when it comes to traversing the tree from
> a branch to leaf.
>
> For example, starting from "Corporate HQ" is easy. Simply start where
> "parent is null".
> How do you traverse the org chart when starting at "Div - 1"? Do I need
> some sort of "levels" indicator?
>
> Any thoughts would be appreaciated.
Found it! You need to add the indentation, but it may help:
========================================================================
Subject: Re: need a SELECT script.
Date: Mon, 17 Aug 1998 23:35:25 +0200
From: "Mark D. Stock" <mdstock@informix.com>
Organization: Informix SA
To: DK <devendra.kumar@mci.com>
CC: informix-list@iiug.org, dk@pcisys.net
References: 1
DK wrote:
>
> I've a table which represents a tree structure:
>
> child_id | parent_id
> 2 1
> 3 2
> 4 3
> 8 3
> 5 4
> 6 4
> 9 8
>
> I need to select the
> 1. parent
> 2. child nodes
>
> of any given node.
RECURSION! Excellent! I accept the challenge. Don't know why, but if I
can do it in 4GL, then why not SPL.... Okay, okay, I'll read that list
later. :-)
So, starting with the following I presume:
------------------------------------------------------------------------
CREATE TABLE object
(
child_id SMALLINT,parent_id SMALLINT
);
------------------------------------------------------------------------
INSERT INTO object VALUES (2, 1);
INSERT INTO object VALUES (3, 2);
INSERT INTO object VALUES (4, 3);
INSERT INTO object VALUES (8, 3);
INSERT INTO object VALUES (5, 4);
INSERT INTO object VALUES (6, 4);
INSERT INTO object VALUES (9, 8);------------------------------------------------------------------------
> Like if I choose to find the parent of node=9 then i should get the
> results as
>
> 8
> 3
> 2
> 1
So being simplistic to start with, I assumed each child had only one
parent:
------------------------------------------------------------------------
CREATE PROCEDURE get_parent(child_id SMALLINT)
RETURNING SMALLINT;DEFINE parent_id SMALLINT;
LET parent_id = 0;
SELECT object.parent_id
INTO parent_id
FROM object
WHERE object.child_id = child_id;
IF parent_id != 0
THEN
RETURN parent_id WITH RESUME;
FOREACH EXECUTE PROCEDURE get_parent(parent_id) INTO parent_id
RETURN parent_id WITH RESUME;
END FOREACH;
END IF;
END PROCEDURE;
------------------------------------------------------------------------
EXECUTE PROCEDURE get_parent(9);------------------------------------------------------------------------
Which gives the desired results.
> and if I want to get the children of the node=3, then my result should
> be like
>
> 4
> 5
> 6
> 8
> 9
And a global replace of parent for child and child for parent, in three
passes you understand, gives...... a slight problem. But if we add one
more loop to handle multiple children, we get:
------------------------------------------------------------------------
CREATE PROCEDURE get_child(parent_id SMALLINT)
RETURNING SMALLINT;DEFINE child_id SMALLINT;
LET child_id = 0;
FOREACH SELECT object.child_id
INTO child_id
FROM object
WHERE object.parent_id = parent_id
IF child_id != 0
THEN
RETURN child_id WITH RESUME;
FOREACH EXECUTE PROCEDURE get_child(child_id)
INTO child_id
RETURN child_id WITH RESUME;
END FOREACH;
END IF;
END FOREACH;
END PROCEDURE;
------------------------------------------------------------------------
EXECUTE PROCEDURE get_child(3);------------------------------------------------------------------------
Which also gives the desired results.
> (I can read the results off the cursor?)
Indeed.
> I need a select query/script which can do this work and I can tune it
to
> my
> requirements. Any suggestions will be welcome and if possible please
> e-mail me
> a copy of the solution at dk@pcisys.net
That's my first pass. It can probably stand improvement.
Hope it helps,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //|
|http://www.iiug.org +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////|
| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////|
|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////|
+----------------------+-----------------------------------+-----------+
========================================================================
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+
Steve Romankiw1 wrote: > > I would like to generate an Org Chart using SQL. I am able to generate a > list when working from the root. > However, I have hit a mental block when it comes to traversing the tree from > a branch to leaf. > > For example, starting from "Corporate HQ" is easy. Simply start where > "parent is null". Check out: http://examples.informix.com/frameset.html#top?initial_page=/doc/case_st udies/datablade/node/nodeLOC.html Hope this helps! KR Pb Sent via Deja.com http://www.deja.com/