Children count in Parent/Child relation

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)

Children count in Parent/Child relation

Author

Topic

imp_galo
Starting Member

13 Posts

Posted - 2005-10-22 : 14:09:56

I have a Parent/Child relation in my table as the following example:
TaskId
Name
ParentId
And ParentId is a foreign key related with TaskId.

Is there any way I can query the children count of each task together with the task data?

e.g.: The output would be:
TaskId Name ParentId ChildrenCount
111 'MyTask' NULL 2
222 'MyTask2' 1111 0
333 'MyTask3' 1111 0

Or is better to store the children count in the data base and update it with a trigger or something like it?

Thank you in advance,
Ricardo

imp_galo
Starting Member

13 Posts

Posted - 2005-10-22 : 14:16:24

One more question, how can I delete the child tasks when deleting the Parent?

r937
Posting Yak Master

112 Posts

Posted - 2005-10-22 : 22:15:43

store the children count? eeewwwwww (no offence, eh)

select parent.TaskId 
     , parent.Name 
     , parent.ParentId 
     , count(child.TaskId) as ChildrenCount
  from daTable as parent
left outer
  join daTable as child
    on parent.TaskId = child.ParentId
group
    by parent.TaskId 
     , parent.Name 
     , parent.ParentId

as for deleting the children when deleting the parent, check out ON DELETE CASCADE defined when you declare the foreign key

rudy
http://r937.com/

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-23 : 04:36:13

unfortunately CASCADE does not work with circular relationshpis...

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 06:58:14

If you stored a "path" column, with a list of all the "ancestors", and it would be easy to delete all children:

Say path to "current record" is /1/12/15

then you can delete WHERE Path LIKE '/1/12/15/%'

Also SELECT COUNT(*) WHERE Path LIKE '/1/12/15/%' will tell you the number of descendants (including "self")

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-23 : 10:34:20

I like the path solution, it makes hierarchies very easy to work with.

google hint:
There are articles by "joe celko", "adam machanic" and others, on storing&working with "hierarchies" in "sql".
______________________________________
There is no destination, only the path

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 10:47:19

imp_galo note: You probably can't store the "path" as "/1/12/15" as I've indicated. One of the things you will almost certainly want to do is to ORDER BY the path, and for that you need to be careful that it sorts into the correct order - i.e. the separators must work correctly.

In my experience that has always meant using FIXED LENGTH numbers - so you probably need something more like:

"/0001/0012/0015"

as your "path" - which imposes a limit of "9999" IDs. (Of course you don't then need the "/" at all, you could as easily store the pathy as "000100120015", so that saves some characters; we also don't store the node of the current element itself, on the end of the path, again to save space - but that's a bit six-of-one half-a-dozen-of-the-other

)

Kristen

imp_galo
Starting Member

13 Posts

Posted - 2005-10-25 : 17:26:11

Thank you for the answers, it was very useful!
Regarding the records deletion, what do you think about using a recursive "instead of" trigger?
Maybe it would work.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-25 : 18:02:24

quote:
Originally posted by imp_galo

Thank you for the answers, it was very useful!
Regarding the records deletion, what do you think about using a recursive "instead of" trigger?
Maybe it would work.

instead of triggers cannot recurse.
but you could use the concept and build a temp table in the trigger with all the children (recursively) and delete the lot.

imp_galo
Starting Member

13 Posts

Posted - 2005-10-25 : 19:26:07

R937, is there any way I can use something like "select parent.* group by parent.*"? Or make a separate select statement for selecting only the childrencount would make it simpler?

Kristen
Test

22859 Posts

Posted - 2005-10-26 : 00:37:28

"Regarding the records deletion, what do you think about using a recursive ..."

what do you think about .... hmmm ... "Slow and not scalable"!

Take a set-based solution if you can, loops, cursors and recursion are for CPUs not databases!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources