Please... Masters!!! please help!!! with Query

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 2005 Forums

Transact-SQL (2005)

Please... Masters!!! please help!!! with Query

Author

Topic

sipi41
Starting Member

4 Posts

Posted - 2007-09-06 : 18:48:30

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

38200 Posts

Posted - 2007-09-06 : 18:52:01

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/

sipi41
Starting Member

4 Posts

Posted - 2007-09-07 : 10:45:47

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-07 : 10:49:35

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

sipi41
Starting Member

4 Posts

Posted - 2007-09-07 : 11:13:49

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 13:15:33

Where is the difference between that query and the one from Jeff?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 13:16:17

Because "+" is the same as "SUM".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources