Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive family, but how?


Recursive family, but how?

Author
Message
SeeknFind
SeeknFind
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 41
Hi,

I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:

declare @tv table (member varchar(255), relatedTo varchar(255))

insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all
select 'Peter', 'Nancy' union all
select 'Abby', null union all
select 'Jessica', 'Fred' union all
select 'Joe', 'Frank'



I would like my result to look like this:
familyID   Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank


Thanks a lot!!
hunchback
hunchback
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 639
The trick here is where to start so I used the logic where members are not related to someone (relatedTo is null) or they are related to someone but the member is not on the relatedTo side (John, Peter, Abby, Joe) and consider them head of the family. The rest is to traverse the hierarchy and rank them based on the hof.


with Tree as (
select distinct
A.member as hof, A.member, A.member as relatedTo
from
@tv as A
where
(
A.relatedTo is null
or exists (
select *
from @tv as B
where B.member = A.member and B.relatedTo is not null
)
)
and not exists (
select *
from @tv as B
where B.relatedTo = A.member
)

union all

select
P.hof,
C.member,
C.relatedTo
from
Tree as P
inner join
@tv as C
on P.relatedTo = C.member
and C.relatedTo is not null
)
select
DENSE_RANK() over (order by hof) as rnk,
hof,
relatedTo
from
Tree
order by
rnk,
case when relatedTo = hof then 1 else 2 end,
relatedTo
GO



That is the closer I got.



SeeknFind
SeeknFind
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 41
This is great!! Thanks.
Just one note, this part of the code below I think always resolves as true and I am not sure what it was supposed to do:
   (
A.relatedTo is null
or exists (
select *
from @tv as B
where B.member = A.member and B.relatedTo is not null
)
)


SeeknFind
SeeknFind
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 41
One more note, my data is not normalized. I discovered the scenario below which causes and endless recursion. I can clean it up but just thought I would mention it.

declare @tv table (member varchar(255), relatedTo varchar(255))

insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Jessica', 'Mary'

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search