Thanks for the reply Helen,

I solved the problem using a simple inner join. It was a bit of a duh! as I
had it my head that it needed to be a sub query.

This is for a 'people' database I have been playing around with for far to
long :)
I often have the situation where the same person is involved with several
organizations and so, do not want to recreate their data over and over. I
also want to represent complex company structures that include different
locations, dept's, and people, etc.

The second table I mentioned basically holds names and some identifiers for
those names. The PK of that table links to child_id (so strictly speaking
it's not a child). There is a unique constraint on root/parent/child, I
just didn't show it in this example.

I decided to include the Root field so I could parse it in as a parameter in
a query easily without having to recurse the whole branch.

The query I have ended up with is :

Select l.elink_id, l.root_id, l.parent_id, l.child_id, n.ename_name
from (tbl_entity_lnks sl left join tbl_entity_lnks l
on sl.root_id = l.root_id)
inner join tbl_entity_names n on l.child_id = n.ename_id
where sl.child_id = :child_id

The unfortunate side effect of this is that I end up with sub branches under
the root that are not applicable to the name, as well as the sub branch I
want. (So maybe thats the case for recursing?)

Comments?

Thanks again

Andrew

> The matter of self-referencing tree structures and how to
> maintain them is one that can keep DBAs in debate over the
> coffee-cups until the sun comes up. Still, your problem here is
> caused by either by lack of normalization or ambiguity in the
> structure itself, or both.
>
> From your description, it appears you want it to be possible for
> the same child node to be subtended by multiple parent nodes. Is
> this the case? i.e. you have no unique constraint on root/parent/child?
>
> In a "regular" hierarchy, you would not store root and children
> identifiers in this structure. You would have just "me" and
> "mum" with constraints to prevent "me" being "mum" to myself and
> to prevent "me" being my mum's "mum". You would get the children
> of a node, or the ancestors of a node by recursively querying
> until you ran out of child nodes (or ancestor nodes, in the latter case).
>
> Could you give a bit more of a description of the requirements?
>
> Helen
>
>
> What do you have in the second table you referred to?
>