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..