Applying Unary (recursive) relationship

nhaddad's Avatar
Newbie Member
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.
0
rahulnaskar's Avatar, Join Date: Aug 2004
Go4Expert Member
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...