[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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?
0
JCTDD
Asked:
JCTDD
  • 4
  • 3
1 Solution
 
ThomasianCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now