Re: Subquery problem

Jussi Vainionpää 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?

Subqueries comes in different flavours. Subqueries in the from-clause was first introduced in the sql-92 standard, I believe, and many implementations does not support it.

You could try something like

SELECT m.name, length, count(rating)
  FROM movies m
  LEFT OUTER JOIN ratings r

               ON m.name = r.name
              AND rating = 5

--
Kristian Damm Jensen              | Feed the hungry. Go to 
kristian-damm.jensen_at_capgemini.dk | http://www.thehungersite.com