[thelist] [MySQL] need help with a query

[Paul Cowan]

Joshua Olson wrote:
> Working within the constraints of MySQL, this is about as good as I can
> go. If you were working with MSSQL I could get you to the solution you
> were looking for about a half-dozen ways (some of which start with the
> solution above).

I'm not sure, but don't (reasonably recent) versions of MySQL support 
subqueries? If so, then something like:
	SELECT idmovie, MAX(c1) AS c1, MAX(c2) AS C2... etc
	FROM
	(
		-- ... insert Joshua's query here ...
	) AS JoshuasQuery
should do what Chris is after nicely.

Assuming the version of MySQL in question also supports views (I know 
NOTHING about MySQL), another idea is to create this:
	SELECT
		idmovie
		user_rating,
		count(*) AS RatingCount
	FROM
		ratingstable
	GROUP BY
		idmovie,
		user_rating
as a view (called RatingCounts, for the sake of argument)
		
and then:
	SELECT
		movietable.moviename,
		movietable.whatever else,
		onereviews.RatingCount AS OneCount,
		tworeviews.RatingCount as TwoCount
		... and so on...
	FROM
		movietable
	LEFT JOIN RatingCounts AS OneReviews
		ON (movietable.idmovie = OneReviews.idmovie)
		AND (OneReviews.user_rating = 1)
	LEFT JOIN RatingCounts AS TwoReviews
		ON (movietable.idmovie = TwoReviews.idmovie)
		AND (TwoReviews.user_rating = 2)
	... and so on

Of course, you don't NEED to make it as a view -- you could repeat the 
subquery 5 times, but that would be pretty serious overkill.

Cheers,

Paul