SQL Statement

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.
WaldaIncAsked:
Who is Participating?
 
Faiga DiegelSr Database EngineerCommented:
Did you try this style?

SELECT TableA.*
FROM TableA
WHERE TableA.Part NOT IN
     (SELECT NonPart FROM TableB)
0
 
rshafakianCommented:
SELECT * FROM myTable WHERE part NOT IN (SELECT otherpart FROM myOtherTable)
0
 
WaldaIncAuthor Commented:
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
 
StanyslawCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.