?
Solved

COUNT(*) from multiple tables

Posted on 2005-05-13
6
Medium Priority
?
372 Views
Last Modified: 2008-03-10
I want the total number of records from 2 child tables that are related to a parent table.
This doesn't work:

CREATE PROCEDURE [MyProcedure]
(
      @MyPrimaryKey,
      @Count OUTPUT
)
AS

      SET @Count =
                   SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1
        +
         SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1

      RETURN @Count
GO

Anyone?
0
Comment
Question by:natejacobs
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13996235
Maybe...

CREATE PROCEDURE [MyProcedure]
(
     @MyPrimaryKey,
     @Count OUTPUT
)
AS

declare @temp1 int
declare @temp2 int

SET @temp1 = SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1
SET @temp2 = SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1

@Count = @temp1 + @temp2
RETURN @Count
GO
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13996323
or this one:

CREATE PROCEDURE [MyProcedure]
(
     @MyPrimaryKey VARCHAR(10),
     @Count INT OUTPUT
)
AS

     SET @Count = (SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1 )
       +
        (SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1 )

     RETURN @Count
GO
0
 
LVL 3

Author Comment

by:natejacobs
ID: 13996428
Syntax works, it'll take a few minutes to get it into my app.
Related question:  How would you test this sp in Query Analyzer?

typing
MyProcedure 135

doesn't work

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13996446
Try this:

DECLARE @RowCount INT

EXEC MyProcedure 135, @RowCount OUTPUT

PRINT @RowCount
0
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13996454
Hi natejacobs,

 This is off the top of my head, but you need to do a subselect

 select count(*) as Total1 from table1
 where (select Count(*) as Total2 from Table 2 where ForeignKey = @MyPrimaryKey)


Cheers!
0
 
LVL 3

Author Comment

by:natejacobs
ID: 13996480
Thanks for both answers, rafrancisco
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question