Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count unique from another table (JOIN)

Author  Topic 

chrvik
Starting Member

7 Posts

Posted - 2012-11-14 : 04:20:46
Hi there!

New to both this forum and SQL.
I'm having an issue that hopefully someone here can help me with:

I have a website with a forum. I would like to count how many times each member have written something in the forum. This is done dynamically on "mypage.asp" so that a logged in member can see his og her info.

DB looks like this:


I was trying out a JOIN between table Users and table Forum, but it crashed and burned when I was trying to implement the user session in the SQL to get the right ID.

All help and hints would be much appreciated!

Chris

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-14 : 04:41:28
Please show the statement that you have so far...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

chrvik
Starting Member

7 Posts

Posted - 2012-11-14 : 05:02:11
SELECT ID
FROM USERS
INNER JOIN FORUM ON UserID
WHERE Users.ID=Forum.UserID

I'm not at home now, so I can't give you the error message.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 07:02:11
That statement should have given you a syntax error. If you are trying to find the number of times a given user id has written to the forum, use this:
SELECT COUNT(*) FROM Forum WHERE UserId = @UserId;


For all users
SELECT UserId,COUNT(*) FROM forum WHERE GROUP BY UserId;


For a user given the username:
SELECT 
COUNT(*)
FROM
Users U
INNER JOIN Forum f ON f.UserId = u.UserId
WHERE
u.Username = 'JaneDoe';
Go to Top of Page

chrvik
Starting Member

7 Posts

Posted - 2012-11-14 : 07:36:30
I hereby declare my love for this forum and the people willing to help out, thank you!

Since this info is to appear on a dynamic page, can this SQL be written to suite a user session? (rs_User or MM_ColParam etc..)
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 09:26:22
You can construct the SQL statement in the code-behind of the page, replacing the parameter with the appropriate value.

A better approach though, would be to create a stored procedure with a parameter (such as @UserId) and then call that stored procedure from the client code, passing in the userId of the user who is viewing the page.
Go to Top of Page

chrvik
Starting Member

7 Posts

Posted - 2012-11-16 : 05:01:31
Thank you for your respons sunitabeck!

I have another question as how to add the user session to the statement, but I'm gonna post that as a new question to keep it tidy.

Thanks!
Go to Top of Page
   

- Advertisement -