Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   Applying Unary (recursive) relationship (http://www.go4expert.com/forums/applying-unary-recursive-relationship-t2007/)

nhaddad 26Nov2006 05:37

Applying Unary (recursive) relationship
Hi. I want to apply a unary relationship to my database. I tried a lot, searched a lot, but couldn't find an answer. To make it quick and easy, here's my problem:
iam making a contacts database in MS Access. The contacts table contain first, middle, and last names for each person, and other attributes that doesn't relate to my problem. I want to create a unary relationship (parent-child), where each person have an attribute, that is a foreign key for his or her father or mother's primary key.
What i thought of is to make a supertype/subtype relationship, where each person can be a parent, child, or both.
So the final question would be:
Is there a way to apply unary relationships in MS Access? If yes, how? If no, is it done by using supertype/subtype relationship? If yes, thank you. If no, what should i do?
Thanks for any help.

rahulnaskar 27Nov2006 11:47

Re: Applying Unary (recursive) relationship
Let the table have a simplified structure like mentioned:

contactid: ID of the contact being saved
name: Name of the contact
parentid: His/her parent's ID

The SQL statement will return all contacts and corresponding parent id:

SELECT a.contactid, a.name, b.parentid, b.name
FROM table1 AS a, table1 AS b
WHERE a.parentid=b.contactid;

Hope this helps...

All times are GMT +5.5. The time now is 18:43.