Have you tried an inline statement? SELECT s.*, (select avg(a.rating) from airdates a where a.showID = s.showID) AS 'average' FROM shows s ORDER BY average ASC
I'm not 100% sure on the order by since its an alias but you can try it. This also depends on what version of mysql you're using. Disclaimer: Code written on the fly...not tested. On 11/7/05, Les Mizzell <[EMAIL PROTECTED]> wrote: > > Just looking for ideas... > > Assume I have a database with TV Miniseries > It's a mySQL database. > > TABLES > ----- > 1. SHOWS > a. showID > b. showTITLE > 2. AIRDATES > a. showID > b. airdate > c. rating > > Now, since it's a miniseries, it will have multiple entries in the > AIRDATES table for each night (part 1, 2, 3, blah, blah..). I need to > AVERAGE the rating for all airdates for each specific title, and then > sort on the average. > > I've come up with a couple of VERY convoluted ideas, but the operative > word here is convoluted. > > How would any of you approach this? > > > -- > ----------- > Les Mizzell > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223552 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
