ansaurus

Question

Answer 1

+2 A:

id | parentid | name
---------------------
 1 | null     | node1
 2 | 1        | node2
 3 | 1        | node3

ArsenMkrt 2009-07-15 05:56:19

Exercise: Write a query that returns all decedents from node1.

Tom Lokhorst 2009-07-15 06:03:34

you mean this? select * From tab where parentid = (select id from table where name = 'node1')

ArsenMkrt 2009-07-15 06:07:59

That's hilarious :-) However, you probably should have pointed out a better alternative, like using nested sets model.

ChssPly76 2009-07-15 06:08:04

To **Arsenmkrt** - no, that would be direct descendants. You were asked for all descendants :)

ChssPly76 2009-07-15 06:09:08

Answer 2

+1 A:

SQL Server 2008 has a built-in data-type called hierarchyid to store hierarchical information. Here are some pointers.

And of course you can do this as mentioned by arsenmkrt in databases other than sqlserver2008.

this. __curious_geek 2009-07-15 06:07:12

Answer 3

+1 A:

Here's a pretty decent tutorial which explains why adjacency model proposed by arsenmkrt is less than ideal.

ChssPly76 2009-07-15 06:18:29

+1 I didn't think about that before ;)

ArsenMkrt 2009-07-15 06:23:49

how to design database for binary tree?