SQL join/question

[jeffp]

ok i've got a table visitors containing<br><br>long userid&nbsp;&nbsp;&nbsp;' user id<br>long visid&nbsp;&nbsp;&nbsp;&nbsp;' visitor id<br>date visdate&nbsp;&nbsp;' date of visit<br><br>then a table called userfull that contains user information:<br><br>long userid<br>string handle ' name of user<br><br>basically i'm tracking visitors (other members) who visit another user.&nbsp;&nbsp;I want to display this info, so I'm using the query below which works.&nbsp;&nbsp;However I would like to know how many visitors someone has in this same query.&nbsp;&nbsp;But whenever I add a COUNT(*) as numvisitors it gives me an error.. what's the best way to do this?&nbsp;&nbsp;The example will show all of user #3's visitors.&nbsp;&nbsp;Any help would be appreciated.<br><br>SELECT visitors.userid, visitors.visid, visitors.visdate, userfull.handle FROM visitors, userfull WHERE visitors.userid = 3 AND userfull.userid = visitors.visid ORDER BY visitors.visdate DESC<br><br>

 

[karluk]

If I understand what you want, you can probably get this via a join.&nbsp;&nbsp;You want a count by userid of the visitor table, which can be obtained with a &quot;group by userid&quot; clause.&nbsp;&nbsp;However you also need detailed information on each visit in the same query.&nbsp;&nbsp;Try the following:<br><br>select visitors.userid, visitors.visid, visitors.visdate, &quot;viscount&quot;<br>&nbsp;from visitors, (select a.userid &quot;user&quot;,count(*) &quot;viscount&quot; from visitors a group by userid)<br>where visitors.userid=3<br>&nbsp;&nbsp;and visitors.userid=&quot;user&quot;<br><br>Note that I've omitted your &quot;userfull&quot; table for clarity.