Archives of the TeradataForum

Message Posted: Fri, 09 May 2003 @ 13:00:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

How about something like this. It is not recursive, but should enable you to retreive all needed rows. Then you could display it in some hierarchal tree using your favorite gui.

create table managers(
high_lvl_manager  char(1),
manager char(1)
person char(1)
) primary index person;

/*in this case manager 'a' is the manager of all managers*/
/*persons b, c, d are managed by a*/
/*persons e and f are managed by d*/

insert into managers (high_lvl_manager, manager, person) values ('a','a','a');
insert into managers (high_lvl_manager, manager, person) values ('a','a','b');
insert into managers (high_lvl_manager, manager, person) values ('a','a','c');
insert into managers (high_lvl_manager, manager, person) values ('a','a','d');
insert into managers (high_lvl_manager, manager, person) values ('a','d','e');
insert into managers (high_lvl_manager, manager, person) values ('a','d','f');

/* get all people reporting to a */
select * from managers where high_lvl_manager='a' and person<>high_lvl_manager;

/* get all people reporting to d*/
select * from managers where manager='d';

	<Prev	Next>		<<First	<Prev	Next>	Last>>