Union count help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Union count help

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2007-05-10 : 17:24:44

What I need to do is check three tables and make sure and ActivityID doesn't already exist when the user tries and update or insert.

I can test for a count <> 0, but when I run this query, and say I have the activity in two of the three tables, I don't ever get "2". I can show three in my results b/c I have more than 1 duplicated ActID in a table. Basically, this count is working, since I need to check for <> 0, but the sum total is 100% right.

Am I missing something:
Declare @WPID nvarchar(100)
Declare @ActID nvarchar(100)
Set @CommonID = 'H1.01.01.01-FY08-0.1'
Set @ActID = 'H1010101A1001'

Select sum(mycount) From (
SELECT count(*) as mycount FROM TBL1 WHERE (ActID = @ActID) and (CommonID = @WPID)
UNION

SELECT count(*) as mycount FROM TBL2 WHERE (ActID = @ActID) and (CommonID = @WPID)
UNION

SELECT count(*) as mycount FROM TBL3 WHERE (ActID = @ActID) and (CommonID = @WPID)
) as myTable

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-10 : 18:14:29

Why dont just use EXISTS?

IF EXISTS ( SELECT * FROM TBL1 WHERE ActID = @ActID and CommonID = @WPID)

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-11 : 00:07:02

Try this...

Select count(*) From (
SELECT * FROM TBL1
UNION all

SELECT * FROM TBL2
UNION all

SELECT * FROM TBL3
) as myTable WHERE (ActID = @ActID) and (CommonID = @WPID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources