title: Get all subpaths in a hierarchical query in Oracle
body: <p>I've a table that contains a list of sales departments as follows:</p>
  
  <pre><code>+-------+---------------+-----------+
  |DEPT_ID|DEPT_NAME      |DEPT_PARENT|
  +-------+---------------+-----------+
  |5500   |World          |           |
  |5510   |Region 1       |5500       |
  |5511   |Cell 1 Region 1|5510       |
  |5512   |Cell 2 Region 1|5510       |
  |5513   |Cell 3 Region 1|5510       |
  |5514   |Cell 4 Region 1|5510       |
  |5515   |Cell 5 Region 1|5510       |
  |5520   |Region 2       |5500       |
  |5521   |Cell 1 Region 2|5520       |
  |5522   |Cell 2 Region 2|5520       |
  |5530   |Region 3       |5500       |
  |5531   |Cell 1 Region 3|5530       |
  |5532   |Cell 2 Region 3|5530       |
  |5540   |Region 4       |5500       |
  |5533   |Cell 1 Region 4|5540       |
  |5534   |Cell 2 Region 4|5533       |
  |5590   |Region 5       |5500       |
  |5591   |Cell 1 Region 5|5590       |
  +-------+---------------+-----------+
  </code></pre>
  
  <p>I need a query that returns all possible subpaths. So for the first three rows it should be as follows:</p>
  
  <pre><code>5500 5510
  5500 5511
  5510 5511
  </code></pre>
  
  <p>so for each possible subpath it would return the first and last department of the path. It is fairly easy to get the path by doing this:</p>
  
  <pre><code>    SELECT d.*, LTRIM (SYS_CONNECT_BY_PATH (dept_id, '-'), '-') AS PATH
        FROM depts d
  START WITH dept_parent IS NULL
  CONNECT BY PRIOR dept_id = dept_parent
  </code></pre>
  
  <p>but how I can get all possible subpaths?</p>