select count with table join

[fran7]

Hi, I wonder if anyone knows how to do this. I need to get info from another table and cannot get anything to work.
I tried

 ' set rst=conn.execute("select Watchedprofileid , p.pdjobtitle,M.WatchinguserID from tblWatches M INNER join tblother P ON M.WatchinguserID = P.UserId  where (Select Count(*) from tblWatches where WatchinguserID = " & rs("userID"))

I also cannot get it to return results greater than 5, for example. I added >5

where WatchinguserID = " & rs("userID") > 5)

for example but that didnt work.

<%    
 dim rst
'on error resume next
 
set rst=conn.execute("Select count(*) from tblWatches where WatchinguserID = " & rs("userID"))
 
if rst(0)> 0 then
        response.Write "<b> watching " & rst(0) & " </b>"
    end if
    rst.close
%> 

Thanks in advance for any help.
Richard

[code green]

You need to expalin what you are trying to do. This meaningless

Hi, I wonder if anyone knows how to do this. I need to get info from another table and cannot get anything to work.

Post SQL code and not VB.

[nbiswas]

How about this

' set rst=conn.execute
 
("
 
select 
 
Watchedprofileid 
, p.pdjobtitle
,M.WatchinguserID 
 
from tblWatches M 
 
INNER join tblother P 
 
ON M.WatchinguserID = P.UserId  
 
where M.WatchinguserID > 5
 
And M.WatchinguserID = " & rs("userID")
 
) 

Hope this helps..