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