Re: need a SELECT script.
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"!|/ ////////|
+----------------------+-----------------------------------+-----------+