Joining tables to find NOT IN records

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)

Joining tables to find NOT IN records

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-18 : 16:23:13

Here is my Question:

I have 2 Tables A & B
Table A has 2 fields (code & description)
Table B has 3 fields (Code & info_1, info_2)

I need to find all records of Table B, which are corresponding to the codes that are not in Table A.
I can find it by
Select * from B where B.code not in (Select code from A)
or by Using "Not Exists"

What I need to know :
Is there any way of doing this by Joining the 2 tables by some means.

Srinika

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-18 : 16:34:08

Try this no clue if it'll work, but it doesn't hurt to try.

select TableB.code
from TableB left outer join TableA on
TableA.code = TableB.code
where TableA.code is null

or

select TableB.code
from TableB left outer join TableA on
TableA.code = TableB.code
where TableA.code <> TableB.code

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-18 : 16:37:53

Thanks,

The 1st one worked great.

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:38:45

quote:
Originally posted by humanpuck

select TableB.code
from TableB left outer join TableA on
TableA.code = TableB.code
where TableA.code is null

This is how to find the rows that are missing in the right table. So this should be Srinika's solution if I understand it correctly.

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 16:52:19

quote:
Originally posted by humanpuck

sorry bout that try this

select AcctName, Address, AcctContact, OrderContact
from Accts join Orders on Accts.Acct=Orders.Acct
group by AcctName, Address, AcctContact, OrderContact
having max(OrderDate) = (select max(orderdate) from orders)

Wrong thread.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources