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.

select m.name, r.rating, count(*)
from movies m, ratings r
where m.name = r.name
group by m.name, r.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.

Or it doesn't support that syntax of subquery. I don't use PostgreSQL so I can't say for sure. You might want to look up the subquery syntax in your docs.

Just to see if subqueries work in your dbms, try these. These queries don't solve your problem, but they will at least tell you if you can do subqueries:

/* Movies with at least one rating */
select name
from movies
where name in (select name from ratings)

/* Total number of ratings for a movie, subquery version */ select m.name,

       (select count(*)
        from ratings r
        where m.name = r.name)

By the way, how do you handle different movies that happen to have the same name?

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Fri Jan 19 2001 - 17:04:31 CET