[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