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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Please... Masters!!! please help!!! with Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sipi41
Starting Member

USA
4 Posts
Posted - 09/06/2007 :  18:48:30  Show Profile  Reply with Quote
Hello to everyone of you here!! I have a question that is breaking my head... the story is... I have two tables... in both tables I have a field that contain a number... what I want to know is to know how many records on both tables have this number...

Table 1: temporal_announcements
table 2: announcements

the number I'm looking for must be (according to the tables) in the following fields:

Table 1: temporal_announcements.user_unique_key
Table 2: announcements.under_account

so I did the following but doesn't work:

SELECT count(*) FROM DBO.temporal_announcements, DBO.announcements
WHERE temporal_announcements.user_unique_key = '07262007-020830' AND announcements .under_account = '07262007-020830'

but this return invalid data... a number that is not correct... what can I do... I just want to count how many records has this number in x field in both tables... any idea?? the number is the same for both tables... the only thing that changes is the field name in every table. please help, thank you.

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 09/06/2007 :  18:52:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try this:


select count(*)
from dbo.temporal_announcements t
inner join dbo.announcements a
on t.user_unique_key = a.under_account
where a.under_account = '07262007-020830'

Or perhaps you want the two counts of both tables summed up?

select sum(countt)
from
(
select count(*) as countt
from dbo.temporal_announcements
where user_unique_key = '07262007-020830'
union
select count(*)
from dbo.announcements
where under_account = '07262007-020830'
) t


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Edited by - tkizer on 09/06/2007 18:54:08
Go to Top of Page

sipi41
Starting Member

USA
4 Posts
Posted - 09/07/2007 :  10:45:47  Show Profile  Reply with Quote
Please help!!!

This didn't solve the problem... I have 4 records (2 in each table) and the result display only 2... the first example didn't work... and the second one seems to work the first time only... but not anymore... here's the example...

select sum(countt) as valor from
(select count(*) as countt from dbo.temporal_announcements where user_unique_key = '07262007-020830'
union
select count(*) as countt from dbo.announcements where under_account = '07262007-020830'
) t

any ideas???

Billy
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 09/07/2007 :  10:49:35  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
select sum(countt) as valor from
(select count(*) as countt from dbo.temporal_announcements where user_unique_key = '07262007-020830'
union all
select count(*) as countt from dbo.announcements where under_account = '07262007-020830'
) t

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

sipi41
Starting Member

USA
4 Posts
Posted - 09/07/2007 :  11:13:49  Show Profile  Reply with Quote
Thank you for all your support!!! -- you are the masters!! -- Well, i found another solution... in fact what we get are 2 numbers that we can easily sum...

after hours of looking to this problem I did the following... your last answer also works... here it is:

select (select count(*) from dbo.temporal_announcements where user_unique_key = '07262007-020830') + (select count(*) from dbo.announcements where under_account = '07262007-020830') as resultss

but thank you!!!

Billy
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/07/2007 :  13:15:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Where is the difference between that query and the one from Jeff?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/07/2007 :  13:16:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Because "+" is the same as "SUM".


E 12°55'05.25"
N 56°04'39.16"
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.06 seconds. Powered By: Snitz Forums 2000