CODEWITHSUNDEEP
mysqljoinsubquery
meder omuraliev
meder omuraliev

Reputation: 186562

Access parent column in subquery inner join

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

This query works fine, except the project ID is hardcoded ( 9000 in the subquery ). I'm trying to replace 9000 with projects.id or when aliased, p.id. This results in a syntax error:

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

Which results in [Err] 1054 - Unknown column 'p.id' in 'on clause'. Also tried adding an ON:

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

Which apparently is wrong as well. I'm just not grokking how to access p.id?

Upvotes: 1

Views: 1529

Answers (2)

ArunasR
ArunasR

Reputation: 2005

try this:

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 ( tl.tasks_id = t.id ) 
        GROUP  BY t.projects_id
        HAVING t.projects_id = p.id) AS foo 
FROM   projects AS p 
WHERE  1 = 1 
       AND p.isactive = 1 
GROUP  BY p.id

Upvotes: 1

Ann L.
Ann L.

Reputation: 13965

I'm not a MySQL maven, but have you tried this for your subquery?

                SELECT     Sum(tl.hours) AS total 
                FROM       tasks t 
                INNER JOIN tasklogs tl 
                ON         tl.tasks_id = t.id
                WHERE      t.projects_id = p.id
                GROUP BY   t.projects_id

Upvotes: 2

Related Questions