1

If I have the following schema & data and am employing the closure table pattern:

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 1 | 2 | 2 | 0 | | 2 | 2 | 12 | 1 | | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | | 10 | 12 | 12 | 0 | | 11 | 13 | 13 | 0 | | 12 | 14 | 14 | 0 | | 13 | 15 | 15 | 0 | | 9 | 17 | 20 | 1 | | 8 | 17 | 19 | 1 | | 7 | 17 | 18 | 1 | | 6 | 17 | 17 | 0 | | 14 | 18 | 18 | 0 | | 15 | 19 | 19 | 0 | | 16 | 20 | 20 | 0 | +----+----------+------------+--------+

What would my join query back to my main table look like to obtain all the sibling rows of row id 2?

+----+----------+------------+--------+ | id | ancestor | descendant | length | +----+----------+------------+--------+ | 3 | 2 | 13 | 1 | | 4 | 2 | 14 | 1 | | 5 | 2 | 15 | 1 | +----+----------+------------+--------+

Alexander Trauzzi
  • 7,277
  • 13
  • 68
  • 112

1 Answers1

2

The siblings of a given node would have the same ancestor. However, this would include "1" as well as your list:

select t.*
from table t 
where t.ancestor = (select ancestor from table t2 where t.id = 2);

In your table, I am not sure what it means for ancestor to be the same as descendant. But, I think the following is the query you want:

select t.*
from table t 
where t.ancestor = (select ancestor from table t2 where t2.id = 2) and
      t.ancestor <> t.descendant and
      t.id <> 2;

EDIT:

You can do this as an explicit join like this:

select t.*
from table t join
     table t2
     on t.ancestor = t2.ancestor and
        t2.id = 2 a
where t.id <> 2 and
      t.ancestor <> t.descendant;

Note: I also added the condition t.id <> 2 so "2" is not considered a sibling of itself.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I should have clarified a bit more, but is there a way to do this using a join? I'm making use of an ORM and would like to model all the connections as relations. – Alexander Trauzzi Mar 02 '14 at 02:43
  • One issue I realized is that I don't readily know that the ancestor is "2". I need to join from my main data table, to this one to obtain all the siblings, only knowing the one row of this closure table. – Alexander Trauzzi Mar 02 '14 at 04:21
  • @Omega . . . "2" isn't the ancestor. It is the `id` of the row you want the siblings of. – Gordon Linoff Mar 02 '14 at 04:27
  • Yeah, I won't be using that ID when I reference the table. I'll be coming in using either ancestor or descendant (most likely descendant), which are IDs pointing back to the table being related to here. – Alexander Trauzzi Mar 02 '14 at 04:33
  • Summarized: I only know an id from the row of the closured table I'm coming from. I want the descendant value for every sibling entry in the closure table so that I can join back to the closured model itself. – Alexander Trauzzi Mar 02 '14 at 04:39