Adding Count table to query

[fran7]

Hi, The below 3 table join with select works fine.

 
   SQLQuery="Select top 5 L.accept,etc,etc, "
 
SQLQuery= SQLQuery & "FROM tblAppsLoginDatabaseBySmo L INNER JOIN tblAppsProfilesDatabaseBySmo P ON L.UserId = P.UserId "
SQLQuery= SQLQuery & " inner join tblWatches T ON P.UserId = T.WatchingUserID "
SQLQuery= SQLQuery & "  and p.userid in (Select WatchingUserID  from tblWatches where  WatchedProfileID <>0 )"
 
SQLQuery= SQLQuery & " order by NEWID() "

I am returning 5 random users who are watching other users. See previous topic.
http://bytes.com/topic/sql-server/an...es#post3768064

What I want is to only return the users who are watching more than 10 other users. I presume it's count(WatchedPr ofileID)>5 but if I try to add it here I get errors. I have tried with having count etc.

SQLQuery= SQLQuery & "  and p.userid in (Select WatchingUserID  from tblWatches where  WatchedProfileID <>0 and (count(WatchedProfileID) >10))"

It might be a syntax thing I am doing wrong.

When I join the tblWatches to the main query

Any pointers would be great
Thanks in advance

Richard

[Rabbit]

You need to join it to a query that does a count of people watched by person watching.

[fran7]

Hi, Thanks, This is how i did it in the end.

(Select WatchingUserID from tblWatches where WatchedProfileID <>0 group by WatchingUserID HAVING COUNT(*) > 5)"

Thanks

Richard