Page 1 of 1

SQl suggestion - join or nested query?

Posted: Tue Jan 04, 2011 5:32 am
by oliur
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]

Re: SQl suggestion - join or nested query?

Posted: Tue Jan 04, 2011 4:15 pm
by Sofw_Arch_Dev
Correct me if I'm wrong but I don't think you need to do either. The report "List of Videos order by most watched ones" does not seem concerned with user_id at all. Since the table has a "TimeWatched" column why don't you just aggregate that column for all distinct videoId?

Or did your state your report incorrectly and userId is somehow involved?

Re: SQl suggestion - join or nested query?

Posted: Wed Jan 05, 2011 5:23 am
by oliur
Apologies, I probably didn't explain it properly. The uid on the other table is very important. All matching is done against it.

I've almost managed to get the query right except it procudes some duplicate rows with different view number. Maybe if you look at the query you will understand it more:

[SQL] select distinct rv.lvID, rv.uid,count(rv.uid) as numberOfViewer from reports_video rv,lookup_video lv where rv.lvID = lv.lvID AND rv.uid IN(SELECT uid FROM `our_db`.`lookup_user` where email like "%hsbc%") AND rv.lastAltered BETWEEN "2010-01-01" AND "2011-01-01" group by rv.uid order by numberOfViewer desc [/SQL]

Here is what you see as output:

Video ID ---- Views
8591 ----- 26 times
8591 ----- 20 times
9045 ----- 14 times
8604 ----- 14 times


What I am not sure is why the first two rows return exact same ID with different view result even after selecting distinct video ID. Or am I doing something silly in the where clause?