complicated count - 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

SQL Server Development (2000)

complicated count

Author

Topic

sgebbie
Starting Member

7 Posts

Posted - 2002-06-20 : 18:15:45

I really need some help here.

I have a table [family]:parent_name, my_name

and I want to pass 'adam' as a parameter and count the number of family members at each generation down 3 generations

the data would be:
god, adam
adam, Michael
Michael, Lucifer
adam, john
John, Matthew
Matthew, David
David, Peter
Peter, Paul
Paul, Wendy

so 'Adam' has 5 members (3 levels deep, john&michael&Lucifer&Matthew&David)
Level1:2 members-John&Michael
Level2:2 members-Matthew&Lucifer
Level3:1 member-David

, David's offspring are ignored.

I cannot understand 'books online' so am pretty much completly stuck and the topics i have seen are similar but still different and confuse me more than anything.
If anyone can help I would really appreciate it
thanks in advance
Stephen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-20 : 19:24:16

I can certainly appreciate that feeling....

You've chosen to implement what's called the adjacency hierarchy model. I would have figured God to use the nested-set model, but that's a different issue

; either way, I'd suggest you take a look at Rob's article on hierarchy's and trees. The reason why the problem is difficult is because of the model you've choosen to implement. It doesn't lend itself well to answering your question. It's all about the self joins; which is really just a fancy way of saying an inner join where the left and right side are both the same table.

You've made the problem managable by indicating a specific '3' levels. Had you not, I would have converted your data to a differnet mode or probably wouldn't be posting [:d].

Break your problem into smaller pieces. First how many family members does adam have. Well, I presume this to mean 'how many brothers and sisters does adamn have'. To answer that you need to know adam's parent and then count all of the parents children(minus adam). You will use a self join to get the parents kids and a correlated subquery to specify adam. Like this...


select
    count(*)
from
    family parent
    inner join family children
        on parent.my_name = children.parent_name
where
    exists (
        select 1
        from
            family me
        where
            me.my_name = 'Adam' and
            me.parent_name = parent.my_name ) and
    children.my_name <> 'Adam'

Now that you have that you need the next level. How many kids does adam have.


select
    count(*)
from
    family me
    inner join family mykids
        on me.my_name = mykids.parent_name
where
    me.my_name = 'Adam'

I'm not gonna spell it all out to you, but once you have these counts, its just a matter making each of these count queries a subquery in a greater select statement . . .


select
    (<first count>) +
    (<second count>) +
    (<third count>)

I hope Thou ist pleased. Give St. Peter the nod when my time comes . . .

<O>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-21 : 03:57:05

But family 'trees' are actually directed acyclic graphs. You can't represent DAGs with nested sets.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-21 : 08:07:59

OK, granted, nested-set won't work. I didn't think; I just typed.....

But I don't think a DAG will do it either. I'm pretty sure Edipus screwed that up for us.

<O>

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-21 : 09:08:44

Sure it does:


Laius ---.
         | 
         |
         V
      Oedipus ----> Polynices<---.
         ^    |---> Eteocles <---|
         |    |---> Ismene   <---|
         |    `---> Antigone <---|
         |                       |
Jocasta -'-----------------------'

Edited by - Arnold Fribble on 06/21/2002 09:09:55

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-21 : 09:28:06

Hum...I suppose you're right...in order for it to mess the DAG model I guess he would have had to been his own father.

I've been made the fribble once again...that's ok with me, there could be worse things.

<O>

sgebbie
Starting Member

7 Posts

Posted - 2002-06-23 : 19:34:20

Thanks guys I got it working,
what would happen if I didnt want to go down 3 levels only?
If i wanted the total offspring under adam

is this possible?

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources