title: Mixing together Connect by, inner join and sum with Oracle
body: <p>I need help with a oracle query.</p>
  
  <p>Here is my setup:</p>
  
  <p>I have 2 tables called respectively "tasks" and "timesheets". The "tasks" table is a recursive one, that way each task can have multiple subtasks. Each timesheet is associated with a task (not necessarily the "root" task) and contains the number of hours worked on it.</p>
  
  <p>Example:</p>
  
  <h1>Tasks</h1>
  
  <p>id:1 | name: Task A | parent_id: NULL</p>
  
  <p>id:2 | name: Task A1 | parent_id: 1</p>
  
  <p>id:3 | name: Task A1.1 | parent_id: 2</p>
  
  <p>id:4 | name: Task B | parent_id: NULL</p>
  
  <p>id:5 | name: Task B1 | parent_id: 4</p>
  
  <h1>Timesheets</h1>
  
  <p>id:1 | task_id: 1 | hours: 1</p>
  
  <p>id:2 | task_id: 2 | hours: 3</p>
  
  <p>id:3 | task_id:3 | hours: 1</p>
  
  <p>id:5 | task_id:5 | hours:1
  ...</p>
  
  <h1>What I want to do:</h1>
  
  <p>I want a query that will return the sum of all the hours worked on a "task hierarchy". If we take a look at the previous example, It means I would like to have the following results:</p>
  
  <p>task A - 5 hour(s) | task B - 1 hour(s)</p>
  
  <h1>At first I tried this</h1>
  
  <pre><code>SELECT TaskName, Sum(Hours) "TotalHours" 
  FROM (
      SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, 
      ts.hours as hours
      FROM tasks t INNER JOIN timesheets ts ON t.id=ts.task_id
      START WITH PARENTOID=-1
      CONNECT BY PRIOR t.id = t.parent_id
      )
  GROUP BY TaskName Having Sum(Hours) &gt; 0 ORDER BY TaskName
  </code></pre>
  
  <p>And it almost work. THe only problem is that if there are no timesheet for a root task, it will skip the whole hieararchy... but there might be timesheets for the child rows and it is exactly what happens with Task B1. I know it is the "inner join" part that is causing my problem but I'm not sure how can I get rid of it.</p>
  
  <p>Any idea how to solve this problem?</p>
  
  <p>Thank you</p>