You're describing an adjacency list model, which is the most obvious
structure but arguably not the best.
Another data structure for this kind of thing is described at:
http://groups.google.com/groups?selm=%230%23O0OiFBHA.1932%40tkmsftngp04&;
oe=UTF-8&output=gplain
Cheers,
Jim
-----Original Message-----
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Sunday, July 13, 2003 7:29 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
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]
