How do I join two tables to get a row count?

I have two tables: Thread and Reply and they both have a field called UserID

I need to know the number of rows in both tables where UserID="Chris"

I can do this with two stored procedures and add the results together:

SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'

SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'

but there must be a better way. Can this be written as one stored procedure with some sort of join?

Thanks, Chris

Try something like this:

select
(select count(user_id) from table1 where userID = 'blah' )
+ (select count(user_id) from table2 where userID = 'blah' )
as totalCount

|||

you can do one stored proc, get the counts into 2 different variables and send them out as OUTPUT parameters, so you get all the data in 1 trip.

You can also do it in one query as follows:

SELECT COUNT(*) as Count1, (SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris') as Count2

FROM Thread WHERE Thread.UserID='Chris'

|||

Darrell,

That worked great. Thanks,

Chris

How do I get the UserID column value of the User that just has

Hello ML,
Thanks for the reply. May be I am using them in the back ground . All
I do is use controls in the front to get the job done. But I never had
a situation or need to customize these controls and hence had to look
into the aspnetdb database where to make changes inorder to accomplish
my task.
Like I said this is my first time that I have a need to grab the
UserId and push that into another table along with the DelaerShipID. So
I just needed some suggestions on how do I do it.
As a test I Executed the stored proce aspnet_Users_CreateUser and it
returns the @.UserId value here is the output from the Management
Studio...
****************************************
*********************
USE [aspnetdbTest]
GO
DECLARE @.return_value int,
@.UserId uniqueidentifier
EXEC @.return_value = [dbo].[aspnet_Users_CreateUser]
@.ApplicationId = '8203a4f9-995b-4506-aec0-075551d77d02',
@.UserName = N'TestUser',
@.IsUserAnonymous = false,
@.LastActivityDate = N'4/11/2006',
@.UserId = @.UserId OUTPUT
SELECT @.UserId as N'@.UserId'
SELECT 'Return Value' = @.return_value
GO
****************************************
***********************
and the output I got is the @.UserID
'303B2D60-788C-4F96-9930-7E5BA173D165'.
Now my question is :
To get this @.UserID value from the aspnetdb to my Business Layer what
event do I need to use in my code behind page?
Thanks
-LI'm sorry if I sounded harsh. There were no ulterior motives.
You'd better ask this in a more appropriate news group (depending on the
programming language used in your asp code-behind). Basically, you need to
trap the value being returned from the procedure in a local variable in your
asp method by invoking the SQL procedure appropriately. You need to declare
the UserId parameter as an output parameter and assign its value to the loca
l
variable. (Yes, it's obvious that I'm more at home in T-SQL.)
ML
http://milambda.blogspot.com/

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql