Re: error with Subquery

On fös, 2006-12-08 at 13:58 +0300, Max Bondaruk wrote:
> Hi!
>
> error with Subquery alias...

> SELECT *,(SELECT COUNT(id)
> FROM articles a WHERE a.lft < articles.lft AND a.rgt > articles.rgt) AS depth
> FROM articles
> where (depth < 3)
> ORDER BY lft

you cannot refer to depth in the where because it is not
an attribute of the table in the FROM list.

it may be more obvious if we replace the subquery with a constant:
SELECT *, 999 as depth
  FROM articles
  WHERE (depth < 3)

however you should be able to do
SELECT * FROM
  ( SELECT *,
           (SELECT COUNT(id) FROM articles a
              WHERE a.lft < articles.lft
              AND a.rgt > articles.rgt
           ) AS depth
    FROM articles
  ) AS foo
WHERE  (depth < 3)
ORDER BY lft

gnari