| Subject | Re: [ib-support] Sub Query problem |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-02-12T03:08:15Z |
At 04:42 PM 11-02-01 -0700, you wrote:
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?
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
>Hello all,Subqueries are scalar (i.e. they blow up if they can logically return multiple rows, since their purpose is to unambiguously poplulate a single column in a single row).
>
>I have two tables that tie together to represent a many to many data
>structure. One entity_name can be use in many 'branches' of related data.
>
>I have a table of four fields :
>
>ELink_id integer primary key
>Root_id integer
>Parent_id integer
>Child_id integer
>
>Root_Id represents the ELink_id of the node at the start of the new branch.
>
>When I select a particular node (child_ID), I want to know what other
>branches it exists in. I use the simple sql:
>select Root_id
>from tbl_entity_lnks
>where child_id = :child_id
>
>Then I get a summary of the branch numbers (root_id) that I am interested
>in. I tried to then tie it together with :
>
>Select l.elink.id, l.root_id, l.parent_id, l.child_id
>from tbl_entity_lnks l
>where l.root_id = (select sl.Root_id from tbl_entity_lnks sl
>where sl.child_id = :child_id )
>
>But I get a 'multiple rows in singleton select' error.
>
>I would like to make this into a stored proc. Any ideas on how to get it to
>operate?
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?
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________