Tree Structured Queries in SQL Server 7.0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tree Structured Queries in SQL Server 7.0

Author

Topic

k006b
Starting Member

10 Posts

Posted - 2002-03-06 : 06:53:23

This is something like a referral program

I am Planning to use SQL Server7.0 or oracle (pl suggest the best.)

A webmaster can refer at a max of three downline members under him. I need a query or SP to get the total no of downline members referred by a webmaster. The query or Stored procedure should search entire records for the downline
members for a webmaster (ie topline webmaster).

Table Structure:

CREATE TABLE [usr] (
[referer_id] [int] NOT NULL ,
[code] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
) ON [PRIMARY]
GO

Some values for you to check:

insert into usr values(2,'abc1')
insert into usr values(2,'abc2')
insert into usr values(2,'abc3')
insert into usr values(3,'abc4')
insert into usr values(3,'abc5')
insert into usr values(3,'abc6')
insert into usr values(6,'abc7')
insert into usr values(4,'abc8')
insert into usr values(7,'abc9')
insert into usr values(9,'abc10')
insert into usr values(9,'abc11')
insert into usr values(10,'abc12')
insert into usr values(6,'abc13')

Example 1: total no of downline members referred by NO 2 is 13
Example 2: total no of downline members referred by NO 6 is 6 (no 6
referred abc7,abc9,abc10,abc11,abc12,abc13)
Example 3 : total no of downline members referred by NO 4 is 1 (no 4
referred abc8)

Like this it goes...
Tree structure
1
3
9
27
81
243
729
2187

awaiting for your advice
Thanks for your valuable time - K006B

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-06 : 10:22:13

you will most likely want to write a recursive stored procedure that will return the count that you need.

If you need help writing this than dont hesitate to ask.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-06 : 11:06:14

You might also check out the articles we've already written on this topic (http://www.sqlteam.com/SearchResults.asp?SearchTerms=tree). There's a recursive stored proc in there somewhere.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

k006b
Starting Member

10 Posts

Posted - 2002-03-06 : 11:15:19

Dear Yakoo,

thank you very much for your response.

I would appreciate if you could give the recursive stored
procedure that will return the count.

thanks - k006b

quote:

you will most likely want to write a recursive stored procedure that will return the count that you need.

If you need help writing this than dont hesitate to ask.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-06 : 11:59:12

quote:

If you need help writing this than dont hesitate to ask.

Not to be offensive or anything but I said if you need help then I would help you. You are just asking for a quick answer.

Follow the link that graz mentioned there and see what you can come up with. If you are still stuck we can help you. You might want to search the forums becuase I believe there were talks about sp recurrsion already.

And to get you started a recursive sp would look like this


CREATE PROCEDURE sp_Factorial (@number integer) AS

declare @temp integer
declare @passVariable integer

IF @number = 1
	RETURN 1
ELSE BEGIN
	SELECT @passVariable = @number -1

        --recursive call
	EXEC @temp = sp_Factorial @number = @passVariable
	RETURN @number * @temp
END
GO

then just call exec @Result = sp_Factorial 8
and @result will contain 40320

k006b
Starting Member

10 Posts

Posted - 2002-03-07 : 01:23:05

Thank you for your quick response.

My SP

*********************************************

CREATE PROCEDURE ab
@iReferID int
AS

SET NOCOUNT ON

DECLARE @iCode int

IF NOT EXISTS( SELECT 1 FROM sysobjects WHERE NAME = 'ReferralOutput' )
CREATE TABLE ReferralOutput( referer_id int, Code int )

SELECT @iCode = Code FROM usr where referer_id = @iReferID

IF @iCode IS NOT NULL
BEGIN
INSERT INTO ReferralOutput SELECT @iReferID, @iCode

EXECUTE ab @iCode
END

SELECT * FROM ReferralOutput
DROP TABLE ReferralOutput
GO
*****************************************

I tried the above SP i am not getting the count as required also i am getting Maximum stored procedure nesting level exceeded (limit 32). bcoz of this message i am stuck

To be very frank i am a beginner to use SQL Server7.0
I dont think that i can write such a COMPLEX SP at this point
of time so i have to get HELP from persons like you who helps
beginners.

I would appreciate if you could give me the SP at the earliest.

Thanks & Regards,
K006B

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-07 : 01:45:34

Just stating the obvious - it would appear that your code is never getting out of recursion. ie @iCode is never null, and so the procedure always recurs.

Unhelpfully, I can't get to my SQL Server because my sysadmin has managed to disable my account. Ho hum, I'll try and help you tomorrow..

cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

k006b
Starting Member

10 Posts

Posted - 2002-03-07 : 06:42:43

thanx for your reply.

expecting a solution for my problem....

BFN - K006B

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2002-03-07 : 07:18:26

Type "hierarchical information"
in Index of BOL.
You will get the readymade SP for your problem.

---------------------------
Sandesh - The Messanger

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-07 : 08:38:52

quote:
thanx for your reply.

expecting a solution for my problem....

k006b-

Just to make you aware that WE ARE trying to help you, and that NONE OF US are getting paid to help you. Almost everyone on SQL Team posts here while they are at their job, so they are taking time away from their normal duties to offer their assistance. It is unreasonable for you to EXPECT a solution from us, and ungrateful for the help you've gotten so far. Even if you are a beginner at SQL Server, it's not an excuse for someone else to do your work for you.

There are other solutions available on SQL Team, and as Sandesh noted, in Books Online. The SQL Team forums also have some possible solutions for you:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=2763
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=3578
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=4995

Please notice that I found these links in about 2 minutes using the Forum Search feature on SQL Team. 2 minutes is not much time. It's fortunate that someone thinks this time and effort is well spent to help you solve your problem, but unfortunate that apparently you can't invest this time and effort yourself.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-07 : 17:39:28

Methinks he doth expect too much. And remember, we are mere

s.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources