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?