whats wrong with my 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

whats wrong with my logic?

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 04:24:11

table1 = 500 rows
table2 = 1071560

table1.col1 = table2.col2 = 359
table1.col1 <> table2.col2 = 535779641 <---- instead it should return 141..

whats wrong? seems like it s table2 rows X table1 rows..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-30 : 04:26:55

how about showing us your whole query... i smell a cross join happening
try with left join

select *
from table1 left join table2 on table1.col1 = table2.col2
where table2.col2 is null

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 04:27:49

What is the primary key of both tables ?

How did you join the table ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 04:29:38

In joins dont use <>, instead make use of not exists

--Matched
Select * from table1 T where exists
(select * from table2 where col2=t.col1)

--Unmatched
Select * from table1 T where not exists
(select * from table2 where col2=t.col1)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 04:29:56

I think you spirit might be on the right track

you probabaly did this for the 359

select *
from   table1 t1 join table2 t2
       on t1.col1 = t2.col1

and this for the 535779641

select *
from   table1 t1 join table2 t2
       on t1.col1 <> t2.col1

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 04:32:44

well for the 359, i just use this

select count(*) from table1,table2 where table2.col1 = table1.col1

for the 535779641,
select count(*) from table1,table2 where table2.col1 <> table1.col1

what is the best query for this? thanks guys..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 04:34:13

Use Madhivanan's method

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 04:37:58

Didnt you read the replies?

Madhivanan

Failing to plan is Planning to fail

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 04:42:42

yah madhivanan, ur query works..but can i use it like this?

INSERT INTO table1 (column names)
SELECT col1,col2,'PRE',col3,'2',update_date,'1/1/9999 11:59:59 PM','N','1','1'
FROM table2,table1
WHERE NOT EXISTS (select * from table2 where table2.col1=table1.col1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 04:48:32

Try

INSERT INTO table1 (column names)
SELECT col1,col2,'PRE',col3,'2',update_date,'1/1/9999 11:59:59 PM','N','1','1'
FROM table2 t
WHERE NOT EXISTS (select * from table1 where col1=t.col1)

Madhivanan

Failing to plan is Planning to fail

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 04:55:51

ITS WORKSS!!!!!

thanks guys!!! especially madhivanan..thanks alot..

one more thing..ill be running this against table1(10mils rows) table2 (1mil rows)

beside 2 inserts, there will also update..

i use rowcount to maximize the performance..is there another way? :D

thanks guys.thankss...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-30 : 06:07:17

for such large table left join usually works best.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-30 : 06:42:44

Thx Spirit!!

its the 1st time i heard left join, google it but cant apply it to my current code..hw to alter my current code to use the left join? or outer join?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-30 : 06:49:07

look at my example above.

and read about it in BOL = books online = sql server help
joins are the first thing you must learn if you want to do anything with sql.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 07:46:40

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources