SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count unique from another table (JOIN)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chrvik
Starting Member

7 Posts
Posted - 11/14/2012 :  04:20:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8513 Posts
Posted - 11/14/2012 :  04:41:28  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 11/14/2012 :  05:02:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/14/2012 :  07:02:11  Show Profile  Reply with Quote
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 - 11/14/2012 :  07:36:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/14/2012 :  09:26:22  Show Profile  Reply with Quote
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 - 11/16/2012 :  05:01:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000