Query Help - 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)

Query Help

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2007-04-20 : 04:43:45

i have two table t1 and t2 with following column

CID have varchar datatype and
EID have numeric datatype

I want the to check the record those are not equal to CID and EID from t2

How I can check the combination of CID and EID with not in..

for this i have used join with not equal condition but it is not working

select * from t1,t2
where t1.CID != t2.CID and
t1.EID != t2.EID

Plz tell me how I can do this?

SQLTeam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 04:52:15

[code]Select t2.*
from t2 LEFT JOIN t1
on t2.CID = t1.CID and t2.EID = t1.EID
Where t1.CID IS NULL and t1.EID IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

shubhada
Posting Yak Master

117 Posts

Posted - 2007-04-20 : 04:59:06

Thanks. It is working.

Can u plz explain me the t1.CID IS NULL and t1.EID IS NULL ?

t2.CID = t1.CID and t2.EID = t1.EID - This condition will return the matched record. then how IS null will work?

Plz explain me......
this will helpful for me in future...

SQLTeam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 05:03:06

"t2.CID = t1.CID and t2.EID = t1.EID - This condition will return the matched record. then how IS null will work?"

That's the reason to use LEFT JOIN. In case of LEFT JOIN, all the records from the table on left side of join (i.e. t2 in this case) are shown irrespective of whether there are any matching records in the right hand side table. So for all those records which are unmatched, NULL value is shown in key columns (i.e. EID and CID in this case) of right hand table...and since you wanted to see just unmatched records, I added IS NULL condition to show just them.

I hope it is clear now.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources