Hiya. I take it you mean ancestors rather than descendants.
For finding descendants I've been using tables like this:
ID ParentID Lineage
100 0 /100
101 100 /100/101
102 100 /100/102
103 101 /100/101/103
104 103 /100/101/103/104
You can find children by using LEFT(). A problem with this scheme is you
need to know the maximum length of the IDs in the Lineage field. I've been
using 8 hex digits as the width of the IDs there just to be safe. Not sure
if this would work for what you need.
Another thing you could do is create another table to hold hierarchy
relationships.
The data above would appear in this table like so:
ID AncestorID
101 100
102 100
103 101
103 100
104 100
104 101
104 103
As you can see the deeper a record is in the hierarchy, the more records it
would generate.
----- Original Message -----
From: "bruce" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, July 14, 2003 2:29 AM
Subject: query help!!!
> Hi...
>
> I have a question....a problem actually!!!
>
> I'm trying to figure out how to determine the descendents of a given child
> would be. The table defs are...:
>
> CREATE TABLE ItemTbl
> (
> id int(5) NOT NULL auto_increment,
> itemname varchar(25) NOT NULL default '',
> unique (itemname),
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
>
> #
> # assettypetbl
> #
> # b. douglas july 10,03
> # added status. allows user from web to modify the asset obj
> # type. user is able to set items within an asset type
> # to be added/deleted from asset type.
> #
> #
> # Used to store the name/parentid of a given item. the table is used
> # in conjunction with the ItemTbl. This table permits duplicate
> # item names, as a given item can be a child of multiple parent items.
> # In other words, the item can belong to multiple item structures
> #
> #
> # The table contains the following information:
> #
> # itemname varchar(25) NOT NULL default '',(this gets the image/thumb)
> # parent_id int(5) NOT NULL default '',
> #
>
> CREATE TABLE atbl
> (
> itemname varchar(25) NOT NULL default '', #(this gets the image/thumb)
> parent_id int(5) NOT NULL default '',
> ) TYPE=MyISAM;
>
>
> #
> # test data
> #
> insert into itemtbl (itemname) values ('tire');
> insert into itemtbl (itemname) values ('rim');
> insert into itemtbl (itemname) values ('hub');
> insert into itemtbl (itemname) values ('wheel');
> insert into itemtbl (itemname) values ('car');
> insert into itemtbl (itemname) values ('engine');
> insert into itemtbl (itemname) values ('window');
> insert into itemtbl (itemname) values ('airplane');
>
> insert into atbl (itemname, parent_id) values ('tire', 4);
> insert into atbl (itemname, parent_id) values ('rim', 4);
> insert into atbl (itemname, parent_id) values ('hub', 4);
> insert into atbl (itemname, parent_id) values ('wheel', 5);
> insert into atbl (itemname, parent_id) values ('car', 0);
> insert into atbl (itemname, parent_id) values ('engine', 5);
> insert into atbl (itemname, parent_id) values ('window', 5);
> insert into atbl (itemname, parent_id) values ('airplane', 0);
> insert into atbl (itemname, parent_id) values ('wheel', 8);
>
> The atbl contains the parent item relationships... for the items...
>
> Baisically, I need a way of determining what the
> parents/grandparents/great-grandparents/etc... are for a given item.... My
> concern is that a user might add an item and a parent, and I would get
into
> an item being a parent of itself...
>
> So..does anyone have a good way that I could create a query to generate
the
> descendents of a given item....??
>
> Thanks for any pointers/information that might help!!!
>
> Regards,
>
> Bruce
> [EMAIL PROTECTED]
> (925) 866-2790
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
