Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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.
chrvik
Starting Member
7 Posts
Posted - 2012-11-14 : 05:02:11
SELECT IDFROM USERSINNER JOIN FORUM ON UserIDWHERE Users.ID=Forum.UserIDI'm not at home now, so I can't give you the error message.
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.UserIdWHERE u.Username = 'JaneDoe';
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..)
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.
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!