SQl suggestion - join or nested query?
Posted: Tue Jan 04, 2011 5:32 am
If you have a table with some userIDs and another table where you have userIDs,VideoID,TimeWatched columns How do you join these two tables and generate this report?
List of Videos order by most watched ones. (You can only use the userIDs listed in the first table to produce this report)
I've managed to do this which generates all the videos and how many users viewed it in a given time period. But this doesn't take into account the userIDs from the first table.
[SQL]select distinct lvID,count(userID) as numberOfUser from reports_video where lastAltered > "2010-12-01" group by userID order by numberOfUser desc[/SQL]
List of Videos order by most watched ones. (You can only use the userIDs listed in the first table to produce this report)
I've managed to do this which generates all the videos and how many users viewed it in a given time period. But this doesn't take into account the userIDs from the first table.
[SQL]select distinct lvID,count(userID) as numberOfUser from reports_video where lastAltered > "2010-12-01" group by userID order by numberOfUser desc[/SQL]