The report needs to show: MC cat catid videoid MovieTitle and total time watched for each movie
MC is the Master Category. cat is the sub category. It would look like this: Sports Rafting 7 1657 Rafting for Dummies 23 hours 57 minutes 11 seconds Sports SkyDiving 27 9987 The Sky is the Limit 47 minutes 15 seconds etc. hmm looks like I was wrong about a few of those indexes. table: catagories - 140 records Cat varchar(21) not indexed foreign key CatID integer indexed table: salesreport - 7,464,016 records UserID varchar(100) indexed TotalTime integer indexed VideoID integer indexed table: users - 45,563 records UserID varchar(100) indexed primary key ADVID integer indexed table: videos - 21,304 records VideoID integer indexed primary key MovieTitle varchar(75) indexed MC integer not indexed CatID integer not indexed Is that what you were looking for? On 3/30/06, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > > > > > SELECT c.Cat, v.MC, v.CatID, v.VideoID, v.MovieTitle, SUM(s.TotalTime) > > FROM catagories c, videos v, users u, salesreport s > > WHERE u.ADVID > 0 > > AND u.UserID = s.UserID > > AND c.catid = v.catid > > AND v.videoid = s.videoid > > GROUP BY v.VideoID > > ORDER BY c.Cat > > Please include table schema's, cardinality, selectivity and explain > output. > > Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202330 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
