How to exclude records with the same IDs? 1

[iren]

I have a big file and small file. All records IDs in small file match some IDs in the BIG file.
All I need is to exclude those records with the same IDs from big file to get a new file.

How can I do it?

Thank you in advance !

 

[PHV]

One way:
SELECT B.* INTO NewFile
FROM BigFile AS B LEFT JOIN SmallFile AS S ON B.ID = S.ID
WHERE S.ID IS NULL

Another way:
SELECT * INTO NewFile
FROM BigFile
WHERE ID NOT IN (SELECT ID FROM SmallFile)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iren]

PHV,
Let’s say I have multiple IDs for the same members in both Big AND Small files.

I just wonder if it will work in such a case or first Distinct or something needs to be done?

Thank you very much for your help!

 

[AlexCuse]

Either one should work, because you only want to exclude the ID's present in Small File.

Ignorance of certain subjects is a great part of wisdom