A suggestion to get running quickly.
I've done this before by writing a stored procedure that acted in the WHERE
clause. Inside the stored procedure, I'd loop over a select statement until
I ran out of data or found what I was looking for...NOT EFFICIENT at all,
but it will get you going if nothing else will...and if your data isn't too
deep/big, this might be acceptable.
select * from table
where OkToSelectThisOne(#TargetID#, ID)
Function Pseudocode:
function OkToSelectThisOne(int i_target, int i_ID) return boolean;
BEGIN
loop
BEGIN
select super_id into v_super_id from table where id=i_ID
if v_super_id == i_target then exit(TRUE);
id=v_super_id;
EXCEPTION
when NO_DATA_FOUND
exit(FALSE);
when others
log_error();
exit(FALSE);
END;
end loop;
log_error();
exit(FALSE)
END;
Brian
-----Original Message-----
From: MainWizAccount [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 04, 2001 6:42 PM
To: CF-Talk
Subject: Query with nested hierarchy
I need some help in developing either a query or a stored procedure that
will pull out nested data for an organizational chart. Say I have the
following database
ID | SuperID | Name
============================
01 | NULL | Richard Smith
02 | 01 | Karen Smith
03 | 01 | Martin Smith
04 | 01 | Mikey Smith
05 | 02 | Janet Smith
06 | 02 | Bob Smith
07 | 05 | Jimmy Smith
08 | 06 | Kevin Smith
Passing in an ID, I want to retrieve all personnel under that person. So
for example, if I want a list of all of Karen Smith's people, I'd pass in ID
02 and get the following:
Karen Smith
- Janet Smith
-- Jimmy Smith
- Bob Smith
Now the caveat is there is no limit on the depth allowed. So it would be
possible that Karen had 10 people under her, and each of those 10 people
could have 10 people, and of those 10, 5 of them have 6 more people and 5
would have anywhere between 0 - 3, etc... ALso the number of fields in
reality would be more that these three, there would be other information
such as phone #, email, etc...
What's the best way of retrieving this type of information (hopefully in a
singular SP or query call)?
Joseph E. Sheble
a.k.a. Wizaerd
Wizaerd's Realm
Canvas, 3D, Graphics,
ColdFusion, PHP, and mySQL
http://www.wizaerd.com
=================================
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
