Tim,

I am storing a tree of contacts in 2 tables - CONTACT & RELATIONSHIP. (Using 1.04 build 2)

For example, the tree

Root Node
==Tom Brown
==Chelsea Clinton
====Bill Clinton
====Hillary Clinton

is stored as:

CONTACT table
==========
ID     Name
--      ------
0       Root Node
1       Chelsea Clinton
2       Tom Brown
3       Hillary Clinton
4       Bill Clinton

RELATIONSHIP table
=============
ID     Contact1ID   Contact2ID
--      -------------   -------------
1              0                 1
2              0                 2
3              1                 3
4              1                 4

To get the non-leaf nodes (ie Root Node & Chelsea) the following works:

Select *
  FROM Relationship AS R1
  WHERE     EXISTS
     (SELECT *
         FROM Relationship AS R2
         WHERE R1.Contact2ID = R2.Contact1ID)

However, using NOT EXISTS to get the leaf nodes gives the same results.

Select *
  FROM Relationship AS R1
  WHERE NOT EXISTS
     (SELECT *
         FROM Relationship AS R2
         WHERE R1.Contact2ID = R2.Contact1ID)

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au

Richard,

Not an answer to your question, but -because I have a similar project- can
you tell me what was the reason for spliting it into two tables. I have

ID  Name               REL
1    H. de Boer       0
2    J. Jansen          0
3    R. de Boer        1
4    T. de Boer        1
5    H. de Boer        3
.........

which is easy to query too.
Just curious to the why of your approach (always wanting to learn

Regards, Harry




"Richard Harding" <rharding@wck.com.au> schreef in bericht
news:ED9218DC-61E7-4B75-9372-9C73E2B61049@news.elevatesoft.com...
> Tim,
>
> I am storing a tree of contacts in 2 tables - CONTACT & RELATIONSHIP.
(Using 1.04 build 2)
>
> For example, the tree
>
> Root Node
> ==Tom Brown
> ==Chelsea Clinton
> ====Bill Clinton
> ====Hillary Clinton
>
> is stored as:
>
> CONTACT table
> ==========
> ID     Name
> --      ------
> 0       Root Node
> 1       Chelsea Clinton
> 2       Tom Brown
> 3       Hillary Clinton
> 4       Bill Clinton
>
> RELATIONSHIP table
> =============
> ID     Contact1ID   Contact2ID
> --      -------------   -------------
> 1              0                 1
> 2              0                 2
> 3              1                 3
> 4              1                 4
>
> To get the non-leaf nodes (ie Root Node & Chelsea) the following works:
>
> Select *
>    FROM Relationship AS R1
>    WHERE     EXISTS
>       (SELECT *
>           FROM Relationship AS R2
>           WHERE R1.Contact2ID = R2.Contact1ID)
>
> However, using NOT EXISTS to get the leaf nodes gives the same results.
>
> Select *
>    FROM Relationship AS R1
>    WHERE NOT EXISTS
>       (SELECT *
>           FROM Relationship AS R2
>           WHERE R1.Contact2ID = R2.Contact1ID)
>
> --
> Richard Harding
> Windella Computer Knowhow
> 28 Freeman Drive
> Lochinvar NSW 2321
> Phone: 61 2 4930 7336
> Mobile: 0419 016 032
> email: rharding@wck.com.au
>

By splitting it into two tables, each person can have a relationship to more
than one other person (many-to-many relationship).

Greg,

Indeed, obvious. Stupid I didn't thought of that. Maybe because in my
project only father-child relations appear

Regards, Harry

"Greg Bishop" <bishop@porpoisemedia.com> schreef in bericht
news:6018E208-40CE-49F6-AEF5-F3B942350E54@news.elevatesoft.com...
> By splitting it into two tables, each person can have a relationship to
more
> than one other person (many-to-many relationship).
>
>

Harry,

Having as single table means that we are mixing information regarding people and their
relationships.  It is not a normalised model.

If you want a tree structure (ie a person cannot have a relationship to more than 1
person) you can define constraints to enforce this and other constraints to ensure that
the tree remains well organised.

As Greg said, you can also define relationships to more than one person and you can define
the type of relationships. (eg spouse, child, the manager of).

This model is easier to generalise to other hierarchies such as parts in an assembly (a
bolt is a part of a door and also part of a widget) or a subtask in a task (making the
pastry is part of making an apple pie).

It is easy to recursively traverse a subtree in Delphi.


--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone: 61 2 4930 7336
Mobile: 0419 016 032
email: rharding@wck.com.au

Richard,

<< However, using NOT EXISTS to get the leaf nodes gives the same results.
>>

Seems to be a bug in 1.04 - the NOT operator has no effect on the EXISTS
predicate in the WHERE clause.

Ole Willy Tuv

Richard,

<< However, using NOT EXISTS to get the leaf nodes gives the same results.
>>

I will look into this and make sure it is fixed in the next build.

--
Tim Young
Elevate Software
www.elevatesoft.com

Richard,

<< Seems to be a bug in 1.04 - the NOT operator has no effect on the EXISTS
predicate in the WHERE clause. >>

As a workaround, you can use the IN predicate:

select *
from Relationship
where Contact2ID in
(
 select Contact1ID
 from Relationship
)

select *
from Relationship
where Contact2ID not in
(
 select Contact1ID
 from Relationship
)

Ole Willy Tuv

Thanks Ole and Tim . . .

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au