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
