title: Access parent column in subquery inner join
body: <pre><code>SELECT p.id                      AS 'project_id', 
         p.hours                   AS 'budgeted_hours', 
         p.workedhours             AS 'worked_hours', 
         (SELECT Sum(tl.hours) AS total 
          FROM   tasks t 
                 INNER JOIN tasklogs tl 
                         ON ( t.projects_id = 9000
                              AND tl.tasks_id = t.id ) 
          GROUP  BY t.projects_id) AS foo 
  FROM   projects AS p 
  WHERE  1 = 1 
         AND p.isactive = 1 
  GROUP  BY p.id 
  </code></pre>
  
  <p>This query works fine, except the project ID is hardcoded ( 9000  in the subquery ). I'm trying to replace <code>9000</code> with <code>projects.id</code> or when aliased, <code>p.id</code>. This results in a syntax error:</p>
  
  <pre><code>SELECT p.id                      AS 'project_id', 
         p.hours                   AS 'budgeted_hours', 
         p.workedhours             AS 'worked_hours', 
         (SELECT Sum(tl.hours) AS total 
          FROM   tasks t 
                 INNER JOIN tasklogs tl 
                         ON ( t.projects_id = p.id
                              AND tl.tasks_id = t.id ) 
          GROUP  BY t.projects_id) AS foo 
  FROM   projects AS p 
  WHERE  1 = 1 
         AND p.isactive = 1 
  GROUP  BY p.id 
  </code></pre>
  
  <p>Which results in <code>[Err] 1054 - Unknown column 'p.id' in 'on clause'</code>. Also tried adding an <code>ON</code>:</p>
  
  <pre><code>SELECT   p.id          AS 'project_id', 
           p.hours       AS 'budgeted_hours', 
           p.workedhours AS 'worked_hours', 
           ( 
                      SELECT     Sum(tl.hours) AS total 
                      FROM       tasks t 
                      INNER JOIN tasklogs tl 
                      ON         ( 
                                            t.projects_id= p.id 
                                 AND        tl.tasks_id=t.id ) 
                      GROUP BY   t.projects_id ) AS foo 
  on t.projects_id = p.id
  FROM     projects AS p 
  WHERE    1=1 
  AND      p.isactive = 1 
  GROUP BY p.id
  </code></pre>
  
  <p>Which apparently is wrong as well. I'm just not grokking how to access <code>p.id</code>?</p>