Re: Trouble with subqueries

Hello Jussi,

Once, Friday, January 19, 2001, 12:34:50 PM, you wrote:

JV> I have the following two tables:

JV> create table movies (
JV>   name varchar(80),
JV>   info varchar(80),
JV>   length int,
JV>   primary key (name)
JV> );

JV> create table ratings (
JV>   name varchar(80),
JV>   userid varchar(10),
JV>   rating char(1),
JV>   foreign key (name) references movies,
JV>   primary key(name, userid)
JV> );

JV> The tables contain movies and users' ratings of the movies.
JV> I would like to get a listing of all the movies along with how many
JV> users have given the movie some particular rating. The first solution
JV> that I came up with was this:

JV> SELECT name, length, fives
JV>   FROM movies,
JV>        (SELECT name as rname,
JV>                count(*) as fives
JV>           FROM ratings
JV>           WHERE rating='5'
JV>           GROUP BY name)
JV>   WHERE name=rname;

JV> but in PostgreSQL 7 it just gives me this error message:
JV> ERROR:  parser: parse error at or near "("
JV> I have previously used similar queries in Oracle where they have worked,
JV> so it would seem to me that PostgreSQL doesn't support subselects after
JV> all despite all the claims.
JV> Am I doing something wrong or/and is there some another way of making
JV> this query that would work in PostgreSQL?


If I understand correctly it must looks like this:
SELECT name, length,       (SELECT count(*)          FROM ratings          WHERE rating='5'          and
rating.name=movies.name)as fives
 
FROM movies
WHERE name=rname;

-- 
Best regards,Yury