Oracle Sibling Structure

Question

I have a structure that I store equal records in a database table. You can think that these records are siblings. For example I have two records in this table; 1=2 and 1=3. And I need a query that will return all siblings of a given record. Let me give an example;

This is my table with two columns:

create table SIBLINGSTEST(col1 number, col2 number);

I have 2 records, 1=2 and 1=3

insert into SIBLINGSTEST values(1,2);
insert into SIBLINGSTEST values(1,3);

I thought using connect by is the best solution for this situation, and write the following query:

SELECT * FROM SIBLINGSTEST 
START WITH (col1 = 1 or col2 = 1) 
CONNECT BY NOCYCLE (
          (PRIOR col1 = col1) or
          (PRIOR col1 = col2) OR
          (PRIOR col2 = col1) or
          (PRIOR col2 = col2))

This query returns correct results, returning both rows.

If I use 2 as a parameter, the query also runs correctly, returning again both rows.

But if I use 3 as a parameter, the query does not run as I expected, returning only the start row.

SELECT * FROM SIBLINGSTEST 
START WITH (col1 = 3 or col2 = 3) 
CONNECT BY NOCYCLE (
          (PRIOR col1 = col1) or
          (PRIOR col1 = col2) OR
          (PRIOR col2 = col1) or
          (PRIOR col2 = col2))

I wonder why the results of 2 and 3 differs. Any help or idea will be appriciated.

Thanks.

Answer 1

I get both rows with your last query as expected:

SQL> SELECT * FROM SIBLINGSTEST 
  2  START WITH (col1 = 3 or col2 = 3) 
  3  CONNECT BY NOCYCLE (
  4            (PRIOR col1 = col1) or
  5            (PRIOR col1 = col2) OR
  6            (PRIOR col2 = col1) or
  7            (PRIOR col2 = col2));

      COL1       COL2
---------- ----------
         1          3
         1          2

However I would not choose to model it this way. If what you really want is to record that 1, 2, 3 are siblings then I would use:

create table siblings_group (group_id number);
create table people (person_id number, group_id number);
insert into siblings_group values (1);
insert into people values (1, 1);
insert into people values (2, 1);
insert into people values (3, 1);

Then to find all siblings of 3:

SQL>  select person_id from people where group_id =
  2    (select group_id from people where person_id=3);

 PERSON_ID
----------
         1
         2
         3