Groups of Groups... - SQL Server Forums

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

Transact-SQL (2000)

Groups of Groups...

Author

Topic

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-02-01 : 19:04:23

What trying here to get groups and groups of other groups

example A and B are subgroups of C, C and D are subgroups of E which would include A and B because they're subgroups of C

/*
grp grpsub
C A
C B
E C
E D
F D
G B
G D
H C
H D
I E

Ask for 'C' should get A,B
Ask for 'E' should get A,B,C,D
Ask for 'F' should get D
Ask for 'G' should get B,D
Ask for 'I' should get A,B,C,D,E

*/

CREATE TABLE [test_grp] (
[grp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[grpsub] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into test_grp values ('C','A')
insert into test_grp values ('C','B')
insert into test_grp values ('E','C')
insert into test_grp values ('E','D')
insert into test_grp values ('F','D')
insert into test_grp values ('G','B')
insert into test_grp values ('G','D')
insert into test_grp values ('H','C')
insert into test_grp values ('H','D')
insert into test_grp values ('I','E')
insert into test_grp values ('I','D')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-01 : 19:54:50

This is kind of a tree thing. If you want to achieve the "tree view" there are some articles about that here, I believe. But here is a solution that satisfies your requirements. It involves a loop but each iteration gets the entire sibling level at once. One iteration for each level of nesting that exists from your starting point.


declare @tb Table
	(grp char(1)
	,grpLevel int)

declare	@lev int
	,@Grp char(1)

Select @lev = 1
	,@Grp = 'C'

insert @tb
Select	grpsub, @lev
From	test_grp
Where	grp = @Grp

While @@rowcount > 0
begin
	Set @Lev = @lev + 1

	insert @tb
	Select distinct grpsub, @lev
	From	test_grp a
	JOIN	@tb b ON a.grp = b.grp
			and b.grpLevel = @lev - 1

	--avoid circular references
	Left JOIN @tb x ON a.grpsub = x.grp
	where x.grp is NULL

End

Select grp from @tb order by grp

Be One with the Optimizer
TG

edit: I didn't include your DDL in my code but you'll need it to run this example.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-01 : 20:24:23

Try reading through: http://www.seventhnight.com/treestructs.asp

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Billpl
Yak Posting Veteran

71 Posts

Posted - 2005-02-02 : 00:21:04

That's the ticket. I have several queries that use a recursive function to drill down a chain of qualifying ID's but it only works for a one to one relationship. It couldn't handle a "tree". I guess you could call it a "stick". I'm also impressed with the circular reference block which could very easily happen in this application.

Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-02 : 07:59:05

Glad to help!

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources