?
Solved

SQL Statement

Posted on 2008-11-19
4
Medium Priority
?
173 Views
Last Modified: 2012-05-05
Hello

I need a SQL statement that selects records where a column value in one table does not exist in another table

For example
TableA has a column called Part. There could be 1 million plus records in this table

TableB has a column called NonPart. This table could have several thousand records.

What I need is a list of all items in TableA where the PART is NOT IN TableB NonPart column.

I've tried several things, nothing seems to work. I end up getting some records that are correct, and some that are not (meaning it does exist in TableB).

Any help is greatly appreciated.
0
Comment
Question by:WaldaInc
4 Comments
 
LVL 15

Accepted Solution

by:
Faiga Diegel earned 2000 total points
ID: 22996989
Did you try this style?

SELECT TableA.*
FROM TableA
WHERE TableA.Part NOT IN
     (SELECT NonPart FROM TableB)
0
 

Expert Comment

by:rshafakian
ID: 22997041
SELECT * FROM myTable WHERE part NOT IN (SELECT otherpart FROM myOtherTable)
0
 

Author Closing Comment

by:WaldaInc
ID: 31518378
No. I didn't try NOT IN.

Man I was trying <> and stuff like that.  I had the inner select correct just didn't use the NOT IN

Thank you so much. I think you save me pulling a few hairs out today.
0
 
LVL 3

Expert Comment

by:Stanyslaw
ID: 22997101
as an alternative - OUTER JOIN may be used.

SELECT TableA.* FROM TableA LEFT OUTER JOIN TableB ON Part=NonPart WHERE NonPart is NULL

In some engines (not sure about MS SQL 2005) it works faster.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question