help with logic... - 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

General SQL Server Forums

New to SQL Server Programming

help with logic...

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-04-27 : 06:02:12

tbl_one hv 8mil rows, tbl_2 have 8k rows...

if
select count(*) from tbl_one
where sub_col1 = 2

return 3mil rows

and

select count(*) from tbl_2
where ad_col1 = '000009'

return 4k rows

HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')

return more than 12 billion rowss?? helpp..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 06:04:10

it cross join between tbl_one and tbl_2
3mil x 4k = 12bil. That's correct

What are you trying to do with joining these 2 tables ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 06:13:17

I think you need to use Inner join

select count(*) from tbl_one T1 inner join tbl_2 T2
on T1.keycol=T2.keycol
where T1.sub_col1 = 2 and T2.ad_col1 = '1234'

Madhivanan

Failing to plan is Planning to fail

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-04-27 : 07:13:38

i want to count total of (sub_col1 = 2 in tbl_one and ad_col1 = '1234' in tbl_2)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 07:21:46

Somthing like this

Select (Select Count(1) From Tbl_One Wher Sub_Col1 =2) as Tbl_one_Count,
(Select Count(1) From Tbl_2 Where ad_Col1 = '1234') As Tbl_2_Count

??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-04-27 : 07:30:54

quote:
Originally posted by madhivanan

I think you need to use Inner join

select count(*) from tbl_one T1 inner join tbl_2 T2
on T1.keycol=T2.keycol
where T1.sub_col1 = 2 and T2.ad_col1 = '1234'

Madhivanan

Failing to plan is Planning to fail

what should i punt in the keycol? sorry..

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 07:40:37

what do you extactly want??

if sub_col1 = 2 in tbl_one has 1000 Records
and ad_col1 = '1234' in tbl_2 has 500 Records

so you want output as 1500??? means sum of this records..

Select (Select Count(1) From Tbl_One Wher Sub_Col1 =2) + (Select Count(1) From Tbl_2 Where ad_Col1 = '1234') As Sum

or Please explain with the some sample data

then query will be somthing like this ..

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 23:08:12

mrjack,

Can you post the structure of tbl_one and tbl_2, some sample data and the expected result ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources