From: | Yury Don <yura(at)vpcit(dot)ru> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Trouble with subqueries |
Date: | 2001-01-19 13:05:42 |
Message-ID: | 11131831464.20010119180542@vpcit.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello Jussi,
Once, Friday, January 19, 2001, 12:34:50 PM, you wrote:
JV> I have the following two tables:
JV> create table movies (
JV> name varchar(80),
JV> info varchar(80),
JV> length int,
JV> primary key (name)
JV> );
JV> create table ratings (
JV> name varchar(80),
JV> userid varchar(10),
JV> rating char(1),
JV> foreign key (name) references movies,
JV> primary key(name, userid)
JV> );
JV> The tables contain movies and users' ratings of the movies.
JV> I would like to get a listing of all the movies along with how many
JV> users have given the movie some particular rating. The first solution
JV> that I came up with was this:
JV> SELECT name, length, fives
JV> FROM movies,
JV> (SELECT name as rname,
JV> count(*) as fives
JV> FROM ratings
JV> WHERE rating='5'
JV> GROUP BY name)
JV> WHERE name=rname;
JV> but in PostgreSQL 7 it just gives me this error message:
JV> ERROR: parser: parse error at or near "("
JV> I have previously used similar queries in Oracle where they have worked,
JV> so it would seem to me that PostgreSQL doesn't support subselects after
JV> all despite all the claims.
JV> Am I doing something wrong or/and is there some another way of making
JV> this query that would work in PostgreSQL?
If I understand correctly it must looks like this:
SELECT name, length,
(SELECT count(*)
FROM ratings
WHERE rating='5'
and rating.name=movies.name) as fives
FROM movies
WHERE name=rname;
--
Best regards,
Yury
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Mantana | 2001-01-19 13:09:30 | (No Subject) |
Previous Message | Tomas Berndtsson | 2001-01-19 12:56:34 | Re: Trouble with subqueries |