SQL excluding IDs

I have 2 tables
i) PrimaryTable1
ii) SecondaryTable2

e.g. rows in PrimaryTable1
ID          Name            Address
123          John Smith      3 Treet St
4325          Jane Mary      4 Apple Ave
1363     Oliver Jo      7 Bridge St

e.g. rows in SecondaryTable2
ID
4325

Question is how to exclude any IDs that appear in the SecondaryTable2?

select * from PrimaryTable1 -- lists all results I want the IDs that appear in SecondaryTable2 to be excluded?
JCTDDAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ThomasianConnect With a Mentor Commented:
select * from PrimaryTable1 T1
WHERE NOT EXISTS (SELECT 1 FROM SecondaryTable2 T2 WHERE T1.ID=T2.ID)

Open in new window

0
 
ThomasianCommented:
Or
select * from PrimaryTable1 T1
WHERE T1.ID NOT IN (SELECT ID FROM SecondaryTable2)

Open in new window

0
 
JCTDDAuthor Commented:
it doesnt work for some reason both IDs in the 2 seperate tables have the same datatype

when I run the code above the ID in the the secondary table still appears?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ThomasianCommented:
Can you post the query you tried?

Here's a test script to show how it works
USE tempdb
GO
CREATE TABLE PrimaryTable1 (ID int, Name varchar(100), Address varchar(100))
INSERT PrimaryTable1
SELECT 123, 'John Smith', '3 Treet St'
UNION ALL SELECT 4325, 'Jane Mary', '4 Apple Ave'
UNION ALL SELECT 1363, 'Oliver Jo', '7 Bridge St'

CREATE TABLE SecondaryTable2 (ID int)
INSERT SecondaryTable2
SELECT 4325

GO

select * from PrimaryTable1 T1
WHERE NOT EXISTS (SELECT 1 FROM SecondaryTable2 T2 WHERE T1.ID=T2.ID)
/*
ID     Name         Address
123    John Smith   3 Treet St
1363   Oliver Jo    7 Bridge St
*/

GO

DROP TABLE PrimaryTable1 
DROP TABLE SecondaryTable2

Open in new window

0
 
JCTDDAuthor Commented:
there is soemthing wrong with the data entered in the secondary table when i run below no results show:

select PrimaryTable1.ProspectID
from PrimaryTable1
INNER JOIN SecondaryTable2 on PrimaryTable1.ProspectID = SecondaryTable2.ProspectID
0
 
kamindaCommented:
I would say NOT EXIST should use instead of NOT IN, as it will not fail your query when there are null values in the ID coulumn of your secondarytable.
0
 
ThomasianCommented:
What is  the datatype of both fields? If it is char/varchar, it is possible that you have spaces causing the comparison to fail.  You can try using LTRIM before comparing the two.

i.e.  LTRIM(PrimaryTable1.ProspectID) = LTRIM(SecondaryTable2.ProspectID)
0
 
JCTDDAuthor Commented:
I figured out the problem I manually typed the values in the secondary table instead of using INSERT

0
All Courses

From novice to tech pro — start learning today.