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 по дате отправления: