![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
![]() |
Thu, Jun 14 2007 8:46 PM | Permanent Link |
Richard Harding | 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 |
Fri, Jun 15 2007 7:25 AM | Permanent Link |
"Harry de Boer" | 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 > |
Fri, Jun 15 2007 12:27 PM | Permanent Link |
"Greg Bishop" | By splitting it into two tables, each person can have a relationship to more
than one other person (many-to-many relationship). |
Fri, Jun 15 2007 4:05 PM | Permanent Link |
"Harry de Boer" | 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). > > |
Fri, Jun 15 2007 6:13 PM | Permanent Link |
Richard Harding | 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 |
Sat, Jun 16 2007 6:32 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Sat, Jun 16 2007 7:55 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Sat, Jun 16 2007 11:17 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Mon, Jun 18 2007 2:10 AM | Permanent Link |
Richard Harding | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |