Postgresql SQL Select items from table1 based on a condition from table2

Question

I am trying to select items from table1 which has a child table2 there is a third table3 involved.

Select j.ccmasterid, 
(Select sum(i.ccmatpullqty) From table2 i 
 Where i.ccmasterid = j.ccmasterid)  pulled
 from table1 j
INNER JOIN table3 s on j.ccstatus = s.sysstatusid and s.ccopenjob=false
where j.ccmasterid LIKE 'W%' and pulled = 0  

This generates an error:

ERROR: column "pulled" does not exist LINE 6: where j.ccmasterid LIKE 'W%' and pulled = 0

If I take the "and pulled = 0" out the the query, it works as one would expect producing a list of records from table1 with the sum of the values in table2 as pulled.

ccmasterid    pulled
W106063            0
W100553            9
W100685            1

WHAT I can't figure out is how to select based on pulled being 0.

Answer 1

Change this query into a subquery, and move WHERE condition to the outer query:

SELECT * FROM (
   Select j.ccmasterid, 
   (Select sum(i.ccmatpullqty) From table2 i 
    Where i.ccmasterid = j.ccmasterid)  pulled
    from table1 j
   INNER JOIN table3 s on j.ccstatus = s.sysstatusid and s.ccopenjob=false
   where j.ccmasterid LIKE 'W%'
) x
WHERE  pulled = 0  

Answer 2

Avoid the correlated subquery which runs for every row in outer query and not once if joining to an aggregate query with GROUP BY clause:

SELECT j.ccmasterid
FROM table1 j
INNER JOIN table3 s 
    ON j.ccstatus = s.sysstatusid AND s.ccopenjob = false
INNER JOIN
   (SELECT i.ccmasterid, SUM(i.ccmatpullqty) AS pulled
    FROM table2 i 
    GROUP BY i.ccmasterid
   ) AS agg
    ON agg.ccmasterid = j.ccmasterid
WHERE j.ccmasterid LIKE 'W%' AND agg.pulled = 0  

Even use CTE

WITH agg AS
    (SELECT i.ccmasterid, SUM(i.ccmatpullqty) AS pulled
     FROM table2 i 
     GROUP BY i.ccmasterid)

SELECT j.ccmasterid
FROM table1 j
INNER JOIN table3 s 
    ON j.ccstatus = s.sysstatusid AND s.ccopenjob = false
INNER JOIN agg
    ON agg.ccmasterid = j.ccmasterid
WHERE j.ccmasterid LIKE 'W%' AND agg.pulled = 0