title: How to fetch all siblings using SQL closure tables pattern (without subquery!)
body: <p>I'm trying to implement the closure table pattern for a simple Menu model, but I've met some difficulties building the query to find all siblings of the current node <strong>without subqueries</strong> (eg. with joins).</p>
  
  <p>There is an <a href="https://stackoverflow.com/questions/22123187/what-query-would-i-use-to-obtain-sibling-records-when-using-closure-tables">old question</a> very similar to mine but doesn't seems to have an answer (or at least I didn't understand it).</p>
  
  <p>Take for example the following simplified scenario (doesn't include zero depth records):</p>
  
  <pre><code>menu:
  +--+--------------+
  | id | title      |
  +--+--------------+
  | 1  | Link 1     |  
  | 2  | Link 1.1   | 
  | 3  | Link 1.2   | 
  | 4  | Link 1.3   | 
  | 5  | Link 1.3.1 | 
  | 6  | Link 1.3.2 |
  +----+------------+
  
  menu_closure:
  +----------+------------+-------+
  | ancestor | descendant | depth |
  +----------+------------+-------+
  | 1        | 2          | 1     |
  | 1        | 3          | 1     |
  | 1        | 4          | 1     |
  | 1        | 5          | 2     |
  | 1        | 6          | 2     |
  | 4        | 5          | 1     |
  | 4        | 6          | 1     |
  +----------+------------+-------+
  </code></pre>
  
  <p>I want to obtain all siblings of the Link 1.1 (id=2) -> Link 1.2 (id=3) and Link 1.3 (id=4).</p>
  
  <p><em>Note: I know only the id of the targeted <code>menu</code> record.</em></p>
  
  <p>Currently, I do the following:</p>
  
  <pre><code>SELECT m.*
  FROM menu AS m
  LEFT JOIN menu_closure AS mc ON mc.descendant=m.id
  WHERE m.id != 2
      AND mc.depth = 1
      AND mc.ancestor = (SELECT ancestor FROM menu_closure WHERE descendant=3 AND depth=1)
  </code></pre>
  
  <p>Another option that I was thinking of was to first get the parent of Link 1.1 and then fetch its children by excluding Link 1.1's id, but I'm searching for a solution with only 1 query.</p>