Re: Subquery problem
Date: Fri, 19 Jan 2001 14:36:18 GMT
Message-ID: <949jd1$e7p$1_at_nnrp1.deja.com>
In article <3A67B6FE.315DB4C_at_cc.hut.fi>,
Jussi =?iso-8859-1?Q?Vainionp=E4=E4?= <jjvainio_at_cc.hut.fi> wrote:
> I have the following two tables:
>
> create table movies (
> name varchar(80),
> info varchar(80),
> length int,
> primary key (name)
> );
>
> create table ratings (
> name varchar(80),
> userid varchar(10),
> rating char(1),
> 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?
>
>
An ANSII-92 standard SQL solution:
SELECT movies.name, MAX (movies.length) AS length, COUNT (ratings.name) AS fives FROM movies LEFT OUTER JOIN ratings ON ratings.name = movies.name AND ratings.rating = '5' GROUP BY movies.name
Note that the MAX aggregate function is safe to use as we grouping by the primary key of the movies table so that length value will always be the same.
This does not use a subselect so you may be able to format it to work in PostgreSQL.
As for PostgreSQL 7 why don't you try a specialist forum for that?
-- Vaughan Powell MCDBA, MCSD, MCSE Sent via Deja.com http://www.deja.com/Received on Fri Jan 19 2001 - 15:36:18 CET