Create Table for TreeView Multi layered SQL Stored Procedure

I have a Client Database that tracks who people have referred to us as new clients.    I would like to get this into a dataset and bind a treeview control to it.   I am trying to write a Stored procedure that pulls that information so I can populate a VB.net datatable.   But, the kicker is that I also want the people that referer referred and on down the line.   For example...

Joe Smith
 - Larry Smith
 - Bob Smith
      - Jane Doe
      - Jack Doe
            - Jill Doe
 - Larry Brown

and on and on.       It will need to iterate as many times as necessary to get all of the info and place into a table.

I have no clue how to do this.

I can easily get the data one time, "Select ClientName from [Client Table] Where Referred By = @Var"  But beyond that it escapes me.

The data structure needed will be something like:

ClientName    Parent_ClientID     ReferredBy_ClientID
Joe Smith            1001                                  0
Bob Smith            2001                            1001
Jane Doe             3001                            2001
Jack Doe              4001                            2001

etc....

Thank you very much for any direction.
rrowe68Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfsamuelCommented:
If you have control over the data structure the proper way to do this is to list the full path of each node as a string.

ClientName    Parent_ClientID     ReferredBy_ClientID              Tree_Path
Joe Smith            1001                                  0                            NULL
Bob Smith            2001                            1001                           1001\
Jane Doe             3001                            2001                           1001\2001\
Jack Doe              4001                            2001                           1001\2001\
Jill Doe                 5001                            4001                           1001\2001\4001\

Then you query can scan the whole path in a single query.

Select ClientName from [Client Table] Where Parent_ClientID=2001 @Var OR Tree_Path LIKE '%2001%'
0
mfsamuelCommented:
It is easy to create the paths on insert just concat Tree_Path and Parent_ClientID of the parent you are inserting a child under.
0
mfsamuelCommented:
Correction:

Select ClientName from [Client Table] Where Parent_ClientID=2001 OR Tree_Path LIKE '%2001%'
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rrowe68Author Commented:
I unfortunately don't have the best data structure for this (at least in the base table).   All I have is the Client and who they were referred by.

So, I am going to have to create this table structure (a temp table?) and I am looking at some sort of looping construct to create this table on the fly.

FYI.
0
Ken SelviaRetiredCommented:

How about;

create procedure p_get_tree (@Var int) as

declare @level int
set @level = 1

select Parent_ClientID id, @level level
into #tree  
from [Client Table] where ReferredBy_ClientID = @var

While @@rowcount > 0
begin
      set @level = @level + 1
      insert #tree
            select Parent_ClientID, @level
            from [Client Table] c
            inner join #tree t on t.id = c.ReferredBy_ClientID
            where t.level = @level - 1
end

select replicate(' ', level - 1) + case when level = 1 then '' else '-' end + ClientName Tree
from #tree
inner join [Client Table] on #tree.id = [Client Table].Parent_ClientID
order by level



Generates this for me;

Tree
----------
Joe Smith
 -Bob Smith
  -Jane Doe
  -Jack Doe
0
puranik_pCommented:
If you are using SQL 2005, consider CTE. much simpler.
Here's a very similar example....
http://msdn2.microsoft.com/en-us/library/ms186243.aspx
0
mfsamuelCommented:
Here is another example of the recursive functions.  This is the best way if you don't have direct table access to add columns, and don't need other database suppot.

http://sqlt.tripod.com/recursivity.htm

Below is this code modified for your application.  The statment SELECT * FROM GetElementChilds ( 1001 )  should return all the child elements.  There is a note that this will not go deeper than 32 recursive calls.
CREATE FUNCTION GetElementChilds ( @ElementID INT) 
RETURNS @result TABLE (ClientName CHAR ( 50 ) , Parent_ClientID INT , ReferredBy_ClientID INT) 
AS 
 
BEGIN 
 
DECLARE @r INT 
DECLARE @i INT 
DECLARE @id INT 
DECLARE @tbl_temp TABLE ( i INT identity, Parent_ClientID INT , ClientName CHAR ( 50 ) , ReferredBy_ClientID INT ) 
 
SET @i = 1 
 
INSERT INTO @tbl_temp 
SELECT Parent_ClientID, ClientName, ReferredBy_ClientID 
FROM [Client Table]  
 
SET @r = @@ROWCOUNT 
 
WHILE ( @i < = @r ) 
BEGIN 
  SELECT @id = Parent_ClientID FROM @tbl_temp WHERE i = @i 
 
  INSERT INTO @result 
  SELECT Parent_ClientID, ClientName, ReferredBy_ClientID 
  FROM @tbl_temp 
  WHERE Parent_ClientID = @id AND ReferredBy_ClientID = @ElementID 
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds ( @id ) 
  END
 
  SET @i = @i + 1 
END
 
RETURN
END

Open in new window

0
rrowe68Author Commented:
MfSamuel.... This is very close.... With a couple of modifications that maybe you can help me with.....  I am pretty Stupid with this Stuff.

1) @ElementID is a char (50) - I am passing the variable (Doe, John)
I don't need any other data from the Client Table (which is just named 'Client', other than then the field Client.cReferredBy.    With the resulting matches, then recursively looking for additional matches.   Thus, building the table with a new cClientID (this is from database - I can do other lookups with this info), ID, ParentID

I am positive what you have coded works, but I have tried to tweek it and cannot get it to work.   Any help is appreciated.
ALTER FUNCTION GetElementChilds ( @ElementID char (50)) 
RETURNS @result TABLE ( Parent_ClientID INT , ReferredBy_ClientID INT) 
AS 
 
BEGIN 
 
DECLARE @r INT 
DECLARE @i INT 
DECLARE @id INT 
DECLARE @tbl_temp TABLE ( i INT identity, cClientID INT , cReferredBy Char (50)) 
 
SET @i = 1 
 
INSERT INTO @tbl_temp 
SELECT cClientID, cReferredBy 
FROM [Client]  
 
SET @r = @@ROWCOUNT 
 
WHILE ( @i < = @r ) 
BEGIN 
  SELECT @id = cClientID FROM @tbl_temp WHERE i = @i 
 
  INSERT INTO @result 
  SELECT  cClientID, cReferredBy 
  FROM @tbl_temp 
  WHERE cClientID = @id AND cReferredBy = @ElementID 
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds ( @id ) 
  END
 
  SET @i = @i + 1 
END
 
RETURN
END

Open in new window

0
mfsamuelCommented:
what is the format of Client.cReferredBy?  is this an ID, "Doe, John" or "John Doe"?
0
mfsamuelCommented:
even better can you paste a subset data?
0
rrowe68Author Commented:

Lets say Rowe, Bob would be top level it would produce these results - so Bob Rowe would be parent to everyone here.  "Rowe, Bob" and "31233" (this is top level parentID) would be passed.   The results should be.... (could be hundreds)

cReferredBy      cClientID      cLastName      cFirstName
Rowe, Bob      64450874      Bartlett      Jackie
Rowe, Bob      316989690      Brady      Randy
Rowe, Bob      222527394      Calderon      Lee

The cClientID is the ID that Bob Rowe referred....(not Bob's ID) - FYI.

So 1st 3 rows of results table would be:

RESULTS TABLE

ParentID    cClientID
31233         64450874
31233         316989690
31233         222527394

I would then want to find out who Jackie referred, etal.  and put his into results table.   And keep building, through until all referrers, etc... can be accounted for.  The format for the search is "Bartlett, Jackie".   So the Temp Table needs to have the concatenated field ''cLastName' + cFirstName'  

RESULTS TABLE

ParentID    cClientID
31233         64450874     <---- Jackies Record
31233         316989690
31233         222527394

- Iterate all of the referers and add them to results table.  (add Jackies referrals)

64450874     123     <---- the clientID that Jackie referred
64450874     321     <---- the clientID that Jackie referred


- and then check those who Jackie referred until there are none, etc...

The goal is to build the tree so we can determine how much business an individual has been responsible for.

Thanks so much for your direction.
0
mfsamuelCommented:
I tested this with the following query, and a similar structure

cClientID      cParentId      cReferredBy
1001            Joe Smith
2001      1001      Bob Smith
3001      2001      Jane Doe
4001      2001      Jack Doe
5001      4001      Jill Doe

SELECT     *
FROM         GetElementChilds(2001) GetElementChilds_1

Returns:
Parent_ClientID      ReferredBy_ClientID
3001                      2001
4001                      2001
5001                      4001

If you want to query by the name of the parent node I can change this to use a name as the parameter, but are you sure it is always unique?  if you use id's it is always a unique parameter.
CREATE FUNCTION GetElementChilds ( @ElementID INT) 
RETURNS @result TABLE ( Parent_ClientID INT , ReferredBy_ClientID INT) 
AS 
 
BEGIN 
 
DECLARE @r INT 
DECLARE @i INT 
DECLARE @id INT 
DECLARE @tbl_temp TABLE ( i INT identity, cClientID INT , cParentId INT) 
 
SET @i = 1
 
INSERT INTO @tbl_temp 
SELECT cClientID, cParentId
FROM [Client]  
 
SET @r = @@ROWCOUNT 
 
WHILE ( @i < = @r ) 
BEGIN 
  SELECT @id = cClientID FROM @tbl_temp WHERE i = @i 
 
  INSERT INTO @result 
  SELECT  cClientID, cParentId
  FROM @tbl_temp 
  WHERE  cClientID = @id AND cParentId = @ElementID 
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds ( @id ) 
  END
 
  SET @i = @i + 1
END
 
RETURN
END

Open in new window

0
mfsamuelCommented:
sorry, didn't wait for your last post.  give me a min to correct.
0
mfsamuelCommented:
I do not see a parent id in the data sample, is this stored in a different table?
0
rrowe68Author Commented:
I can pass the cClientID and the concatenated name of "Rowe, Bob" into the Function - they would be the top level.  Everything else would have to be queried.
0
mfsamuelCommented:
ok I have that.  what is the exact structure of the table the query needs to run on.

is it?

cReferredBy      cClientID      cLastName      cFirstName
Rowe, Bob      64450874      Bartlett      Jackie
Rowe, Bob      316989690      Brady      Randy
Rowe, Bob      222527394      Calderon      Lee

but I do not see a parent id in this table?  is the parent stored in a different table?  if it is that is fine, just want to understand that.  doing a match on just the name may result in multiple overlapping sets if the cReferredBy is not unique.
0
rrowe68Author Commented:
This dataset above is just a subset of data.   Everyone has a cClientID in the Client Table (the cClientID = the ParentID).   The ParentID is just a name for the ClientID (does that make sense)?

Unfortunately, the Match will only be on the name (that is the way the data is in the database).... It will not match the clientID, but rather 'Bartlett, Jackie' for example.
0
mfsamuelCommented:
let me know if this is what you need...

This is the source table:
cReferredBy      cClientID                      cLastName      cFirstName
Rowe, Bob      64450874      Bartlett            Jackie
Rowe, Bob      316989690      Brady            Randy
Rowe, Bob      222527394      Calderon            Lee
Bartlett, Jackie      123                      Jack            Black
Bartlett, Jackie      321                      Simpson            Homer
Simpson, Homer      5432                      Simpson            Bart

This is the function call:
SELECT     *
FROM         GetElementChilds('Rowe, Bob', 31233)

This is the result:
Parent_ClientID      ReferredBy_ClientID
64450874      31233
123            64450874
321            64450874
5432            321
316989690      31233
222527394      31233
      
                  
CREATE FUNCTION GetElementChilds (@ElementID char (50), @ParentId INT) 
RETURNS @result TABLE ( Parent_ClientID INT , ReferredBy_ClientID INT) 
AS 
 
BEGIN 
 
DECLARE @r INT 
DECLARE @i INT 
DECLARE @id INT 
DECLARE @parentNode char (50)
DECLARE @tbl_temp TABLE ( i INT identity, cClientID INT , cReferredBy char (50), parentName char (50)) 
 
SET @i = 1
 
INSERT INTO @tbl_temp 
SELECT cClientID, cReferredBy, (cLastName+', '+cFirstName)
FROM [Client]  
 
SET @r = @@ROWCOUNT 
 
WHILE ( @i < = @r ) 
BEGIN 
  SELECT @id = cClientID FROM @tbl_temp WHERE i = @i 
 
  SELECT @parentNode = parentName FROM @tbl_temp WHERE i = @i
 
  INSERT INTO @result 
  SELECT  cClientID, @ParentId
  FROM @tbl_temp 
  WHERE  cClientID = @id AND cReferredBy = @ElementID 
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds (@parentNode, @id) 
  END
 
  SET @i = @i + 1
END
 
RETURN
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rrowe68Author Commented:
This is what I got?

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)....

I guess it is looping too much.   If possible I can say 10 or 15 is maximum levels, we will go to.

Also, should this part of the code have a Where clause?

INSERT INTO @tbl_temp
SELECT cClientID, cReferredBy, (cLastName+', '+cFirstName)
FROM [Client]  
WHERE cReferredBy = "Rowe, Bob"  - @ElementID to limit just this persons referrals.

thanks
0
rrowe68Author Commented:
This is it!

The only remaining issue is to Assign the cClientID a Variable in this section to pass to the next iteration..

The cClientID is created in the statement above - I need to grab it and to pass to the portion of the code.   HOW?

 IF @@ROWCOUNT > 0
  BEGIN
   INSERT INTO @result
   SELECT * FROM GetElementChilds (@ParentNode, cClientID)
  END


	INSERT INTO @result
	SELECT @ID, cClientID, dbo.xGetReferName(cClientID)
	FROM Client
	WHERE cReferredBy=@ParentNode
 
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds (@ParentNode, cClientID)
  END

Open in new window

0
rrowe68Author Commented:
Thanks for the help !!! Almost there
0
mfsamuelCommented:
try this...
SELECT @TempClientID=cClientID FROM Client WHERE cReferredBy=@ParentNode
 
INSERT INTO @result
SELECT @ID, @TempClientID, dbo.xGetReferName(@TempClientID)
 
  IF @@ROWCOUNT > 0 
  BEGIN 
   INSERT INTO @result 
   SELECT * FROM GetElementChilds (@ParentNode, cClientID)
  END

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.