Recursion, aggregates and table relationships

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)

Recursion, aggregates and table relationships

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-14 : 11:03:11

Geno writes "I have two tables:

foo
foo_id, parentid, name

bar
bar_id, foo_id, name

Obviously, the foo table is the hierarchy, where parentid represents the foo_id of another record in the foo table. A record in the bar table can reference any foo at any level and there can be multiple bars for any given foo.

Given a foo_id, how can I count the total bars for that foo's descendants (children, grandchildren, great-grandchildren, etc.) grouped by the immediate child foos.

For example, I have this data:

nodes
1, 0, "Root"
2, 1, "Foo 1"
3, 1, "Foo 2"
4, 2, "Foo 1.1"
5, 2, "Foo 1.2"

notes
1, 3, "Bar 1"
2, 3, "Bar 2"
3, 4, "Bar 3"
4, 5, "Bar 4"
5, 2, "Bar 5"

Given foo_id=1, the result I want is:
"Foo 1", 3
"Foo 2", 2

Thanks in advance.

G."

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-15 : 03:20:46

G,

Calling your own design foo-bar is awfully cynical. I'll take a stab in the dark at what you're trying to do. As it's awfully late, I'll tackle your first table recursion and post more later.

TABLE1
CHILD, PARENT, ...

In the first table, I guess the first thing you want to do is link together the line of descendants, starting from a parent. So, for a given parent, identify the children. LOOP. For these children, identify their children. Keep looping until there are no more descendants. I guess you'll want this output to look something like this.

OUTPUT1
GENERATION, PARENT, CHILD
0, GOD, Adam
1, Adam, somebody1
1, Adam, somebody2
2, somebody1, theirSon
2, somebody1, theirDaughter
2, somebody2, theirKid

create table output1 (generation int, parent varchar(50), child varchar(50))
create table parents (parent varchar(50))

declare @generation int
select @generation=0
insert parents select parent='GOD'

-- keep looping if there are descendants
while exists
(select * from table1 JOIN parents on table1.parent=parents.parent)
begin

-- JOIN the parents and output
insert output1
select generation=@generation, table1.parent, table1.child
from table1 JOIN parents on table1.parent=parents.parent

delete parents -- wipe out the parents table
insert parents -- insert the children who are now parents
select distinct parent=child from output1 where generation=@generation

select @generation=@generation + 1 -- next generation please

end

Edited by - lou on 02/15/2002 12:23:06

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-15 : 04:13:37

http://www.ietf.org/rfc/rfc3092.txt

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-15 : 12:57:53

Geno, I went back and edited the sample code because I left out the "distinct" when selecting the next generation of parents.

Marching forwards..we're halfway to answering your question. We now have OUTPUT1 which has all the descendants. Suppose you have a second table like this.

TABLE2
CHILD, ...

We are now able to do this:
select count(table2.*)
from OUTPUT1 JOIN TABLE2 on output1.child=table2.child

However, you want to able to group by the direct children or GENERATION1. We have to modify our code to add another column to OUTPUT1 which identifies the GENERATION1 ancestor.

OUTPUT1
GENERATION, PARENT, CHILD, G1
0, GOD, Adam1, Adam1
0, GOD, Adam2, Adam2
1, Adam1, somebody1, Adam1
1, Adam2, person1, Adam2
2, Somebody1, Someone1, Adam1
2, Somebody1, Someone2, Adam1

For generation0, the children are the G1 ancestor. For generation1, the parents are themselves the G1 ancestor. For subsequent generations, the G1 ancestor is known.

create table output1 (generation int, parent varchar(50), child varchar(50), G1 varchar(50))
create table parents (parent varchar(50), G1 varchar(50))

declare @generation int
select @generation=0
insert parents select parent='GOD', G1=null

-- keep looping if there are descendants
while exists
(select * from table1 JOIN parents on table1.parent=parents.parent)
begin

-- JOIN the parents and output
insert output1
select generation=@generation, table1.parent, table1.child,
G1=case @generation
when 0 then table1.child
when 1 then table1.parent
else parents.G1
end
from table1 JOIN parents on table1.parent=parents.parent

delete parents -- wipe out the parents table
insert parents, G1 -- insert the children who are now parents
select distinct parent=child, G1
from output1 where generation=@generation

select @generation=@generation + 1 -- next generation please

end

After this mind bending exercise, you should now be able to do this.

select a.G1, counts=count(a.child)
from
(
select table2.child, output1.G1
from OUTPUT1 JOIN TABLE2 on output1.child=table2.child
) as a
group by a.G1

No, I didn't test this code. Hope this helps.

Edited by - lou on 02/15/2002 13:13:18

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-16 : 13:51:26

quote:

http://www.ietf.org/rfc/rfc3092.txt

If Xerox had time to publish this, no wonder they're almost bankrupt/bankrupted. I guess I don't find the terms foo - bar particularly descriptive or helpful. To each his own.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources