SQL QUERY DOUBT - 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

SQL Server Development (2000)

SQL QUERY DOUBT

Author

Topic

mahroofnm
Starting Member

1 Post

Posted - 2007-02-26 : 06:31:35

Dear Friends
please see the query below :
1 . select * from tempcustomer where code not in (select code
from customer )

Here i am taking a list of customers from tempcustoemr they are
not in customer table ]

but if instead of code if i have to search on the base of two
keys [ code1 and code 2 (it is the combined Primary key ) ]

how i can write the sql command

Mahroof NM
Dubai,UAE

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 06:37:11

I prefer LEFT JOIN over NOT IN:

select t1.*
from tempcustomer t1
LEFT JOIN customer t2
On t1.code1 = t2.code1
and t1.code2 = t2.code2
Where t2.code1 is NULL and t2.code2 is NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 06:38:13

Welcome to SQL Team !!

There are 3 Methods to do so

Method 1--> Using In Query


select * from tempcustomer t1 where 
code not in
(
    select code
    from customer t2 where 
    t1.code2 =t2.code 
)

Method 2 --Using Exists Query


select * from tempcustomer t1 where 
Not Exists 
(
    select 1
    from customer t2 where 
    t1.code2 =t2.code2 And 
	t1.Code1 = t2.Code1   
)

Method 3 --using Left outer Join


select * from tempcustomer t1 
Left outer Join customer t2 
On  t1.code2 =t2.code2 And 
	t1.Code1 = t2.Code1   
where 
t2.Code1 is null and t2.code2 is null

Well Choice is yours.. but i think Exists should be fastest among this..
Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources