Re: Trouble with subqueries

Jussi Vainionpää <jjvainio@cc.hut.fi> writes:

> SELECT name, length, fives
>   FROM movies,
>        (SELECT name as rname,
>                count(*) as fives
>           FROM ratings
>           WHERE rating='5'
>           GROUP BY name)
>   WHERE name=rname;
> 
> but in PostgreSQL 7 it just gives me this error message:
> ERROR:  parser: parse error at or near "("
> I have previously used similar queries in Oracle where they have worked,
> so it would seem to me that PostgreSQL doesn't support subselects after
> all despite all the claims.

It allows subselects in the WHERE clause, but not in the FROM clause. 

> Am I doing something wrong or/and is there some another way of making
> this query that would work in PostgreSQL?

What you can do, is create a view with your subselect, which you can
then use in the FROM clause.


Tomas