Tree drilling down...

• jazzmand

  SSC Rookie

  Points: 33

  More actions

  November 18, 2002 at 6:48 am

  #101311

  I have a table : tbl_organisation

  with the following fields :

  org_id, org_title,org_parent_id

  org_id is the id of the company, title (name) and org_parent_id is the org_id of the parent.

  I want to be able to display a list of all the organisations below one organisation (i.e. rippling through the tree structure I have created). Is there a simply/clean/straightforward way to do this ?

  Thanks

  Jasper

• Greg Larsen

  SSC-Insane

  Points: 20891

  More actions

  November 18, 2002 at 7:47 am

  #441617

  Try a recursive call. Here is some code I got off the list from someone. Sorry I can't remember who. This might help.

  create table employeelist(id int, parentid int, position varchar(20))

  insert into employeelist values (1,null,'CEO')

  insert into employeelist values (11,1,'CFO')

  insert into employeelist values (12,1,'COO')

  insert into employeelist values (13,1,'CTO')

  insert into employeelist values (101,11,'Accounts Manager1')

  insert into employeelist values (102,12,'Operations Manager1')

  insert into employeelist values (103,13,' IT Manager1 ')

  insert into employeelist values (101,11,'Accounts Manager2')

  insert into employeelist values (102,12,'Operations Manager2')

  insert into employeelist values (103,13,' IT Manager2 ')

  insert into employeelist values (1001,101,'Accounts Rep1')

  insert into employeelist values (1002,101,'Accounts Rep2')

  insert into employeelist values (1003,101,'Accounts Rep3')

  insert into employeelist values (1101,102,'Trader1')

  insert into employeelist values (1102,102,'Trader2')

  insert into employeelist values (1103,102,'Trader3')

  insert into employeelist values (1104,102,'Trader4')

  insert into employeelist values (1105,102,'Trader5')

  insert into employeelist values (1106,102,'Trader6')

  insert into employeelist values (1303,103,' DBA')

  alter FUNCTION GetSupervisor

  (@employeeid int)

  RETURNS @groups TABLE (id int, parentid int, position varchar(20))

  AS

  begin

  declare @parentid int

  -- Get the employee supervisor ID

  select @parentid = parentid from employeelist where id = @employeeid

  -- Check if this is the end of the chain.

  if (not (@parentid is null))

  begin

  -- Get the employee supervisor record

  insert into @groups --union

  select * from GetSupervisor(@parentid)

  end

  -- Get the employee record

  insert into @groups

  select * from employeelist where id = @employeeid

  return

  end

  GO

  select * from GetSupervisor(1003)

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP