[thelist] query help!!!
bruce
bedouglas at earthlink.net
Sun Jul 13 20:29:14 CDT 2003
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
bedouglas at earthlink.net
(925) 866-2790
More information about the thelist
mailing list