Trouble with subqueries
| От | Jussi Vainionpää |
|---|---|
| Тема | Trouble with subqueries |
| Дата | |
| Msg-id | 3A67EE1A.B7319298@cc.hut.fi обсуждение |
| Ответы |
Re: Trouble with subqueries
|
| Список | pgsql-sql |
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,
primarykey(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 in PostgreSQL 7 it just gives me this error message:
ERROR: parser: parse error at or near "("
I have previously used similar queries in Oracle where they have worked,
so it would seem to me 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?
В списке pgsql-sql по дате отправления: