Postgres Error: missing FROM-clause entry for table-CodePudding

I have a query and am using left joins. I have the left join clause as follows:

left outer join ( select pup.brokerage_code, pcz.zip, count (pup.aggregate_id) as VerifiedAgentCount
                    from partner_user_profiles pup
                    join partner_user_roles pure on pure.user_profile_id = pup.id
                    join profile_coverage_zips pcz on pcz.profile_id = pup.id
                    where lower(pure.role) = 'agent'
                    and pup.verification_status like 'Verified%'
                    group by pup.brokerage_code, pcz.zip) vac on vac.brokerage_code = b.brokerage_code and pcz.zip = bcz.zip

However I am getting the error message saying that I am missing the FROM entry clause for "pcz" however I aliased the table in the join clause so I am not sure what is wrong.

CodePudding user response：

You have defined the table alias pcz within the sub-select however the alias no longer exists when the outside the sub-select. At the point you have used it the appropriate alias is the one for the entire sub-select, in this case vac. So: vac.zip = = bcz.zip

left outer join ( select pup.brokerage_code, pcz.zip, count (pup.aggregate_id) as VerifiedAgentCount
                    from partner_user_profiles pup
                    join partner_user_roles pure on pure.user_profile_id = pup.id
                    join profile_coverage_zips pcz on pcz.profile_id = pup.id
                    where lower(pure.role) = 'agent'
                    and pup.verification_status like 'Verified%'
                    group by pup.brokerage_code, pcz.zip
                ) vac on vac.brokerage_code = b.brokerage_code 
                     and vac.zip = bcz.zip