Andrew,

>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

is almost the same as

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

except that in the latter you only get the child of interest, whereas the
former gets you information of all siblings as well (assuming root_id is
the ID of the parent and your table's logical PK is root_id, child_id). If
you simply wanted information on the one child, I'd say it's a case of too
long hours and you not being lazy enough as a programmer :-)

Set

At 10:59 12.02.2001 -0700, you wrote:

>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?
>>
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>