Re: Trouble with subqueries

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 each movie, which can be fixed by using select
distint, but I don't quite understand why that happens. Any ideas?

The method suggested by Tomas Berndtsson involving an intermediate view works
nicely too. But this was not quite what I was looking for, as I would like to
have all the movies in the list, also the ones with no ratings. The fives column
should just be zero for those.

I though about creating a view of the union of the movies table and these
results and then doing select max(fives) group by name; from that view, but it
seems that views with unions are not allowed.

But I did find a solution:
SELECT movies.name, movies.length,      COUNT(CASE WHEN ratings.name=movies.name              AND rating='5' THEN true
END)AS fives FROM ratings, movies GROUP BY movies.name, movies.length;
 

But I don't quite understand why I need to have movies.length in the GROUP BY
-clause?