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