Re: Trouble with subqueries

Jussi Vainionpää <jjvainio(at)cc(dot)hut(dot)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