Subquery problem
Date: Fri, 19 Jan 2001 05:39:42 +0200
Message-ID: <3A67B6FE.315DB4C_at_cc.hut.fi>
I have the following two tables:
create table movies (
create table ratings (
name varchar(80),
info varchar(80),
length int,
primary key (name)
);
name varchar(80),
userid varchar(10),
foreign key (name) references movies,
primary key(name, userid)
);
The tables contain movies and users' ratings of the movies. I would like to get a listing of all the movies along with how many users have given the movie some particular rating. The first solution that I came up with was this:
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 this gives me this error message: 'ERROR: parser: parse error at or near "("' in PostgreSQL 7. I have previously used similar queries in Oracle where they have worked, so it would seem that PostgreSQL doesn't support subselects after all despite all the claims. Am I doing something wrong or/and is there some another way of making this query that would work in PostgreSQL? Received on Fri Jan 19 2001 - 04:39:42 CET