title: sql - Deletion in closure table with multiple same paths
body: <p>I have the following hierarchical structure:</p>
  <pre><code>A -&gt; E -&gt; C -&gt; D
  |
  |
  |-&gt; B -&gt; D
  </code></pre>
  <p>Here is the closure table I've come up with:</p>
  <pre><code>| Ancestor | Descendant | Depth |
  |    A     |     A      |   0   |
  |    B     |     B      |   0   | 
  |    C     |     C      |   0   | 
  |    D     |     D      |   0   | 
  |    E     |     E      |   0   | 
  |    A     |     E      |   1   | 
  |    A     |     B      |   1   | 
  |    A     |     C      |   2   | 
  |    E     |     C      |   1   | 
  |    A     |     D      |   3   | 
  |    E     |     D      |   2   | 
  |    C     |     D      |   1   | 
  |    A     |     D      |   2   | 
  |    B     |     D      |   1   | 
  </code></pre>
  <p>I want to remove the link between <code>B</code> and <code>D</code>, and therefore I want to delete the link between <code>A</code> and <code>D</code> (the one of depth <code>2</code>). The problem is that I don't want to delete the link between <code>A</code> and <code>D</code> of depth <code>3</code> since I didn't delete the link between <code>C</code> and <code>D</code>.</p>
  <p>For the moment, here is the SQL statement to list the links I want to delete:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT link.ancestor, link.descendant, link.depth
  FROM closure_table p,
       closure_table link,
       closure_table c
  WHERE p.ancestor = link.ancestor
    AND c.descendant = link.descendant
    AND p.descendant = B
    AND c.ancestor = D;
  </code></pre>
  <p>but this statement give me rows I don't want to delete:</p>
  <pre><code>| Ancestor | Descendant | Depth |
  |    A     |     D      |   2   |
  |    A     |     D      |   3   |  &lt;- As said before, I want to keep this one
  |    B     |     D      |   1   | 
  </code></pre>