title: Recursive join count - Oracle
body: <p>I have this Oracle db structure (something like forum, ok):</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE sections
  (
      section_id           INTEGER NOT NULL ,
      parent_section       INTEGER NULL ,
      section_name         VARCHAR2(256) NOT NULL ,
      section_desc         VARCHAR2(1024) NULL 
  );
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE topics
  (
      topic_id             INTEGER NOT NULL ,
      topic_name           VARCHAR2(256) NOT NULL ,
      section_id           INTEGER NOT NULL
  );
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE messages
  (
      msg_id               INTEGER NOT NULL ,
      msg_text             CLOB NOT NULL ,
      topic_id             INTEGER NULL ,
      msg_date             DATE NOT NULL ,
      user                 INTEGER NOT NULL
  );
  </code></pre>
  
  <p>As you can see, <code>sections</code> have hierarchical structure (by <code>parent_section</code> column).</p>
  
  <p>I want to make two queries:</p>
  
  <ol>
  <li>Get topics count in section by section id, considering all child sections.</li>
  <li>Same with messages instead of topics.</li>
  </ol>
  
  <p>Ok, I'm trying to write first query, but even it doesn't work (return wrong count without errors), I have no idea why:
  </p>
  
  <pre><code>SELECT si, t.TOPIC_ID, COUNT(*)
      FROM (
          SELECT s.SECTION_ID si
              FROM SECTIONS s
          START WITH SECTION_ID = :sectionId
          CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
      )
  LEFT JOIN TOPICS t ON t.SECTION_ID = si
  GROUP BY t.TOPIC_ID, si
  </code></pre>
  
  <p><strong>EDIT:</strong>
  While I'm setting up SQL Fiddle, I got some solution.</p>
  
  <p>Firstly, we should use <code>INNER JOIN</code> instead of <code>LEFT JOIN</code>, because we don't need records with <code>null</code> <code>topic_id</code>.</p>
  
  <p>Secondly, query with <code>COUNT()</code> was wrong, it just returns same records with additional column <code>COUNT</code> with <code>1</code> value. Now I just use <code>SUM(1)</code>:
  </p>
  
  <pre><code>SELECT SUM(1)
    FROM (
      SELECT s.SECTION_ID si
        FROM SECTIONS s
      START WITH SECTION_ID = :sectionId
      CONNECT BY PRIOR SECTION_ID = PARENT_SECTION
    )
  INNER JOIN TOPICS t ON t.SECTION_ID = si
  GROUP BY 1
  </code></pre>
  
  <p><strong>But it still not ideal solution</strong>, if we pass section without topics as <code>START WITH SECTION_ID =</code> argument, we will get nothing as answer. It requires additional check in program for prevent <code>NullPointerException</code>.</p>
  
  <p>Maybe someone know, how can we get <code>0</code> as answer in this case?</p>
  
  <p>SQLFiddle: <a href="http://sqlfiddle.com/#!4/8c093/21" rel="nofollow">http://sqlfiddle.com/#!4/8c093/21</a></p>