Stuff like this is possible in 7.1:
SELECT m.name, m.length, count(r.rating) AS fives
FROM movies m LEFT JOIN (SELECT * FROM ratings WHERE rating = 5) AS r
ON m.name = r.name
GROUP BY m.name, m.length;
I think that would work. You'd want to try different queries with EXPLAIN to
see what looks
Yury Don wrote:
> 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;
This results in several rows for eac
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
Jussi Vainionpää <[EMAIL PROTECTED]> writes:
> 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 giv
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 tabl
