Received: from vpcit.ru (dialup.vpcit.ru [195.38.52.246]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0JD1u005887 for ; Fri, 19 Jan 2001 08:01:56 -0500 (EST) (envelope-from yura@vpcit.ru) Received: (qmail 8198 invoked from network); 19 Jan 2001 13:00:41 -0000 Received: from gercon.vpcit.ru (195.38.52.226) by dialup.vpcit.ru with SMTP; 19 Jan 2001 13:00:41 -0000 Date: Fri, 19 Jan 2001 18:05:42 +0500 From: Yury Don X-Mailer: The Bat! (v1.49) Reply-To: Yury Don Organization: Gercon X-Priority: 3 (Normal) Message-ID: <11131831464.20010119180542@vpcit.ru> To: pgsql-sql@postgresql.org Subject: Re: Trouble with subqueries In-reply-To: <3A67EE1A.B7319298@cc.hut.fi> References: <3A67EE1A.B7319298@cc.hut.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/256 X-Sequence-Number: 925 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