Re: Subquery problem

Thanks! Your message was quite helpful.

> Your solution, even if it worked, would not show those movies where no
> users had given it a rating of 5. This may or may not be a requirement
> but is worth pointing out.

Yes, that was in fact a requirement (and a fault in my first 'solution').

> An ANSII-92 standard SQL solution:
> LEFT OUTER JOIN ratings

This one did not work, because psql doesn't support outer joins (yet).

> This works in SQL Server and is more intuitive:
> SELECT movies.name,
> movies.length,
> fives = (
> SELECT COUNT(*)
> FROM ratings
> WHERE ratings.name = movies.name
> AND ratings.rating = '5'
> )
> FROM movies

But then again this one does work, after changing fives=() into () as fives. It turns out that psql 7.0 supports subselects in all the other places except FROM. Now I'm embarrassed that I didn't think of this myself... Received on Sun Jan 21 2001 - 18:01:30 CST