Thread: subquery question

I've dug around on the Postgres lists for a bit and I haven't found a
good explanation of why this query is not allowed.

SELECT Temp.team_id, Temp.count_agents
FROM (SELECT TR.team_id, COUNT(TR.agent_id) AS count_agents
      FROM teamrel TR
      GROUP BY TR.team_id ) AS Temp
WHERE Temp.count_agents = (SELECT MIN (Temp.count_agents) FROM Temp)

I know that column aliases are not accessible in the WHERE clause, but why can't we access outer table aliases in a
subqueryFROM clause?  Is the table alias rewritten into the select clause? 

-Tom

On Sat, Jan 31, 2009 at 4:13 PM, Tom Raney <twraney@comcast.net> wrote:

> I've dug around on the Postgres lists for a bit and I haven't found a good
> explanation of why this query is not allowed.

> WHERE Temp.count_agents = (SELECT MIN (Temp.count_agents) FROM Temp)

At this point, the sub-select on Temp wont work because Temp does
exist at its level of scope.  It don't fully understand how to use 8.4
new CTE feature, but I might allow something like this to work.  Using
SQL-92, you can make this work using two level of aggregation and
joining the result back to the original table.

SELECT Temp.team_id, Temp.count_agents
  FROM (SELECT TR.team_id, COUNT(TR.agent_id) AS count_agents
          FROM teamrel TR
      GROUP BY TR.team_id ) AS Temp
 WHERE Temp.count_agents = (SELECT MIN( teamcount )
                              FROM ( SELECT COUNT(*) AS teamcount
                                       FROM Teamrel
                                   GROUP BY T1.team_id ));


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug