Using count() in a where clause?

[Martin Edelius]

Hi.

I have two tables. One that contains movies and one that contains grades
that readers have given to the movies. I want to select the most popular
movies and list them. This is not a problem really. The hard part is to
limit the results to movies that have gotten atleast 5 grades (something
like how IMDb does).

I've been going through chapter 7.4 (Functions for use in select and where
clauses) but I can't really say that it helped me. When I do something like:
   select p.ID, b.id, count(b.id) from movies as p, movie_grades as b where
p.ID=b.id group by p.ID order by p.ID desc;
I get this result:
+----+----+-------------+
| ID | id | count(b.id) |
+----+----+-------------+
|  8 |  8 |           1 |
|  7 |  7 |           1 |
|  6 |  6 |           1 |
|  5 |  5 |           1 |
|  4 |  4 |           1 |
|  3 |  3 |           1 |
|  2 |  2 |           1 |
+----+----+-------------+

Indicating that it doesn't return the values I'm looking for (most of the
movies has more than 1 grade). Since I need to match the grades with the
movie I feel that I need to use the "where p.ID=b.id" section but my guess
is that this is also what makes the count statement only return 1.

If I try and use count() in my where clause it's not working at all and if I
read the manual right it's not allowed in anything but group clauses.

If it's not possible to limit the results from MySQL to entries with 5 or
more grades I'm willing to do the filtering in PHP but then I need to get a
correct result from the count(b.id) statement back.

Any ideas?


Best regards,
Martin Edelius

Spirex Digital Design
--------------------------------
www: http://www.spirex.se
Mail: martin.edelius@stripped
Phone: [phone]
Fax: [phone]
Aröds Industriväg 3c
S-422 43 Hisings Backa
--------------------------------
If I haven't got back to you or done what I'm supposed to, let me know again
as I have too much to do for my own good...