Getting Oracle SQL top level parent record from a number of parent/child records

Question

I'm trying to create an Oracle SQL statement to get the top root level parent record from a number of parent, child records on different levels. The table structure is below. The top level root parent in the below is parent_membership_id 53887, this parent record has a number of children, who are also parents to other children. What I want is a query that if I query say member of 200326, the query brings back the root member 53887, or if I query 200322 I get root member 53887. I think you know what I'm trying to do. Thanks in adavance.

 CREATE TABLE MEMBERSHIP_LINK
 ( MEMBERSHIP_LINK_ID        NUMBER(10)          NOT NULL,
   CHILD_MEMBERSHIP_ID       NUMBER(10)          NOT NULL,
   PARENT_MEMBERSHIP_ID      NUMBER(10)          NOT NULL);

Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (35, 53890, 53887);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24475, 200322, 53887);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24476, 200322, 53887);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (34, 53889, 53888);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (5941, 112177, 53889);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (33, 53888, 53890);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24477, 200323, 200322);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24478, 200323, 200322);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24479, 200325, 200323);
Insert into MEMBERSHIP_LINK
   (MEMBERSHIP_LINK_ID, CHILD_MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID)
 Values
   (24480, 200326, 200323);
COMMIT;

Is it intentional that `MEMBERSHIP_LINK_ID`s 24477 and 24478 represent the same relationship? — Sentinel, Dec 13 '16 at 17:25
Both `MEMBERSHIP_LINK_ID`s 24477 and 24478 have the same `CHILD_MEMBERSHIP_ID` and `PARENT_MEMBERSHIP_ID`s (200323 and 200322 respectively), hence `MEMBERSHIP_LINK_ID`s 24477 and 24478 appear to represent the same relationship. — Sentinel, Dec 13 '16 at 18:03

score 0 · Answer 1 · answered Dec 13 '16 at 17:43

Worked out the answer. The SQL is below.

SELECT DISTINCT meli.parent_membership_id
       FROM   MEMBERSHIP_LINK meli
       WHERE  LEVEL = ( SELECT max(level)
             FROM    MEMBERSHIP_LINK meli_in
             START WITH meli_in.child_membership_id = :membership_id
             CONNECT BY meli_in.child_membership_id = PRIOR meli_in.parent_membership_id )
       START WITH meli.child_membership_id = :membership_id
       CONNECT BY meli.child_membership_id = prior meli.parent_membership_id

Sentinel · Accepted Answer · 2016-12-13T18:46:26.203

This one works, but it needs the distinct operator due to records 24477 and 24478 defining the same relationship.

 SELECT DISTINCT ML.PARENT_MEMBERSHIP_ID
   FROM MEMBERSHIP_LINK ml
  WHERE CONNECT_BY_ISLEAF = 1  -- Limit to the "Root" element(s)
  START WITH ML.CHILD_MEMBERSHIP_ID = :decendent_id
CONNECT BY ML.CHILD_MEMBERSHIP_ID = prior ML.PARENT_MEMBERSHIP_ID;

Placing the PRIOR key word in the CONNECT BY clause on the PARENT_MEMBERSHIP_ID causes the tree to be traversed towards the root, and makes the "root" nodes leaves.

If you are trying to find shared ancestry, then a different approach is needed:

WITH ancestry AS
  ( SELECT DISTINCT CONNECT_BY_ROOT CHILD_MEMBERSHIP_ID child_id
         , ML.PARENT_MEMBERSHIP_ID ancestor_id
         , level generation_gap
         , CONNECT_BY_ISLEAF
      FROM MEMBERSHIP_LINK ml
     START WITH ML.CHILD_MEMBERSHIP_ID in (:Descendent_ID1,:Descendent_ID2)
   CONNECT BY ML.CHILD_MEMBERSHIP_ID = prior ML.PARENT_MEMBERSHIP_ID
  )
 SELECT ancestor_id
   FROM ancestry
  WHERE child_id = :Descendent_ID1
INTERSECT
 SELECT ancestor_id
   FROM ancestry
  WHERE child_id = :Descendent_ID2;

From this you can determine the most recent (youngest) shared ancestor, the oldest shared ancestor and the common lineage:

WITH ancestry AS (
   SELECT DISTINCT CONNECT_BY_ROOT CHILD_MEMBERSHIP_ID child_id
        , ML.PARENT_MEMBERSHIP_ID ancestor_id
        , level generation_gap
        , CONNECT_BY_ISLEAF
     FROM MEMBERSHIP_LINK ml
    START WITH ML.CHILD_MEMBERSHIP_ID in (:Descendent_ID1,:Descendent_ID2)
  CONNECT BY ML.CHILD_MEMBERSHIP_ID = prior ML.PARENT_MEMBERSHIP_ID
  ), common AS (
   SELECT ancestor_id
     FROM ancestry
    WHERE child_id = :Descendent_ID1
INTERSECT
   SELECT ancestor_id
     FROM ancestry
    WHERE child_id = :Descendent_ID2
  )
 SELECT MIN( a.ANCESTOR_ID ) keep( dense_rank FIRST ORDER BY a.GENERATION_GAP ) Youngest_Ancestor
      , LISTAGG(a.ANCESTOR_ID, '->') within group (order by a.GENERATION_GAP) common_lineage
      , MIN( a.ANCESTOR_ID ) keep( dense_rank FIRST ORDER BY a.GENERATION_GAP desc ) Oldest_Ancestor
   FROM ancestry a
   JOIN common c
     ON a.ancestor_id = c.ancestor_id
  WHERE a.child_id    = :Descendent_ID1;

Getting Oracle SQL top level parent record from a number of parent/child records

2 Answers2