Help with a GROUP BY query

[logged_with_bugmenot]

Hello everybody

I have a table for movie names and one below it for movie comments with the movie name database having a primary key of movieidx.

I'm trying to return the movies ordered by those with the highest numbers of comments in the database.

The query I'm running is:

SELECT movMovies.moviename,movMovies.movieidx,COUNT(movData.movieidx) AS cnt FROM movMovies,movData
WHERE movMovies.movieidx=movData.movieidx
GROUP BY movMovies.movieidx
ORDER BY cnt DESC

(movMovies holds the movie names and movData holds the comments)

It works fine, however if a movie has no comments (there's a movieidx in movMovies but no records with the corresponding movieidx in movData), it doesn't get returned at all. I think it has something to do with the WHERE clause as it can't be true if there's no corresponding movData record in there, but I'm not quite sure how to proceed.

Your help would be appreciated!

[artacus]

No, you need a LEFT JOIN for the comments.

[fenway]

So:

[code]
SELECT movMovies.moviename,movMovies.movieidx,COUNT(movData.movieidx) AS cnt
FROM movMovies
LEFT JOIN movData ON movMovies.movieidx=movData.movieidx
      GROUP BY movMovies.movieidx
      ORDER BY cnt DESC

[/code]