Recrusive sp

Question

Recrusive sp

chandrika5

SSCrazy

Points: 2734
More actions
January 25, 2006 at 12:25 pm

#96455

I have a table 2 fields only:
group
member
It is possible that a gorup can be a member as well. Eg:
gp A member1
gpA   gpB
gpA   member2
gpB   member3
I need to report all the nested members. In the above case:
gpA has the following members:
member1, member2, member3
I am looking towards a recrusive SP where is all the nested members are returned.

Any advise ?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Can you post your data definition language (DDL)? From that we can help you build the recursive query properly. Also, is this for SQL Server 2000 or SQL Server 2005?

K. Brian Kelley
@kbriankelley

ramses2nd Hall of Fame Points: 3720 More actions · Answer 2

Would This Work:

DECLARE @grp VARCHAR(50)

SELECT MEM

FROM GRPMEM

WHERE (GRP LIKE @grp OR GRP IN (

SELECT GRP

FROM GRPMEM

WHERE GRP IN (SELECT MEM FROM GRPMEM WHERE GRP LIKE @grp)

)) AND MEM NOT IN (SELECT DISTINCT GRP FROM GRPMEM)

chandrika5 SSCrazy Points: 2734 More actions · Answer 3

Here is the schema and sample data:

usrGrps

id int

Name char(1000)

member char(1000)

1 telecom chan1

2 telecom gp1

3 telecom gp2

4 gp1 ell1

5 gp1 gp3

Id is unique

chandrika5 SSCrazy Points: 2734 More actions · Answer 4

hi ramses2nd

I checked the data, the nesting can go upto 4/5 levels. So the I need to have as many inner select distinct.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

How do you tell the difference between users and groups?

K. Brian Kelley
@kbriankelley

chandrika5 SSCrazy Points: 2734 More actions · Answer 6

chandrika5

SSCrazy

Points: 2734

January 25, 2006 at 2:48 pm

#617047

Also this is sqlserver 2000.

Thanks

chandrika5 SSCrazy Points: 2734 More actions · Answer 7

If a user/member does not have users within itself than this is a group.

chandrika5 SSCrazy Points: 2734 More actions · Answer 8

Found a great article, my data and requirements are similar:

http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

This is a good article. I was working up an example query based on your exact table, just haven't had time due to too many things going on at work.

K. Brian Kelley
@kbriankelley

chandrika5 SSCrazy Points: 2734 More actions · Answer 10

Appreciate your time Brian, I knew this was a challange.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 11

Well, I've written them before and actually, I just looked at subject for an article I completed in the last week. If it sees print, it'll be in SQL Server Standard. SQL Server 2005 has what is known as Common Table Expressions (CTEs) and they greatly simplify recursive solutions.

K. Brian Kelley
@kbriankelley