SQL Question

If I store a tree structure (with unlimited level and branch) into the table with following fields

Id int
ParentId int
Name char

where the ParentId is refering to the Id of other record in the same table. Let's say the tree root have Id=0. How can I use one (or fixed number of) select query selecting all ansector of particular Id/Name ?

Example (querying Name=EEE):

Id ParentId Name
0 null AAA
1 0 BBB
2 0 CCC
3 2 DDD
4 3 EEE
5 1 FFF
6 2 GGG

Wanted query result:
0 null AAA
2 0 CCC
3 2 DDD
4 3 EEE

Is it possible ?

Comments

  • From ur explanation, the query and the db structure is the same... i dun get it..? :D

    Neo Gigs
    http://communities.msn.com.my/VisualBasicwithNeo

  • : From ur explanation, the query and the db structure is the same... i dun get it..? :D
    :
    : Neo Gigs
    : http://communities.msn.com.my/VisualBasicwithNeo
    :
    :

    Thx for your reply. Here I quote my example again. The query below is trying get the record set where all record is parent, or parent's parent .. etc, of EEE. So even the query and table structure is the same, the query result contain ONLY:

    1. EEE (Itself)
    2. DDD (EEE's Parent)
    3. CCC (EEE's Parent's Parent)
    4. AAA (EEE's Parent's Parent's Parent)

    Get what i mean ? I still find no way to do such query without a loop.

  • You have no primary key or any related key field to linked em together with some form of relationship, it is hard 4 programmers nor the computer would justify the relationship without any references.

    How do the program know how should the relationship between 1 data o another? What happened when the data growth dynamically from days to days? Should someone has to sit there and do the "old fashion phone operator" manual work rite? No way of coz...

    This table design violate the normalization rules...

    Think to redesign the table in the proper normalization form, it is meant for good programming, easier maintenance and further enhancement goodies...

    Happy day!

    Neo Gigs
    http://communities.msn.com.my/VisualBasicwithNeo

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories