Re: Subquery problem

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?
>
>

Your solution, even if it worked, would not show those movies where no users had given it a rating of 5. This may or may not be a requirement but is worth pointing out.

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.

This works in SQL Server and is more intuitive:

SELECT          movies.name,
                movies.length,
                fives = (
                         SELECT COUNT(*)
                         FROM   ratings
                         WHERE  ratings.name   = movies.name
                         AND    ratings.rating = '5'
                        )
FROM            movies

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/