Parent Child Relationships in Domains / Ad-hoc
What is a Parent Child Relationship?
This concept is well explained here: http://msdn.microsoft.com/en-us/library/aa198072%28v=sql.80%29.aspx
Suppose you have an employee table like the one shown below. The idea here is that every employee has a supervisor except for Sheri Nowmer who is the president (her supervisor id is 0).
This is what the unjoined table looks like, suppose now we want to display the full_name of the supervisor. We need to join the table on itself
Domains don't allow you to join a table on itself, instead they allow you to create a copy of a table, then a join can be made, here's the domain designer and how table copies work within the Joins tab:
Once the table copy is made you will need to now join the tables. In this case a LEFT JOIN was performed to handle the special case of the president, otherwise an inner join would of been ok:
Now we'll want to display the information so that it makes sense to the end user, this can be done in the Display tab:
And finally, here's an ad-hoc report showing for each supervisor, the employees directly underneath him/her: