[thelist] [MySQL] need help with a query
Paul Cowan
evolt at funkwit.com
Sat Mar 27 23:23:02 CST 2004
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
More information about the thelist
mailing list