Solved

SQL excluding IDs

Posted on 2011-03-07
8
251 Views
Last Modified: 2012-08-13
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
Comment
Question by:JCTDD
  • 4
  • 3
8 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 35065094
select * from PrimaryTable1 T1
WHERE NOT EXISTS (SELECT 1 FROM SecondaryTable2 T2 WHERE T1.ID=T2.ID)

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35065116
Or
select * from PrimaryTable1 T1
WHERE T1.ID NOT IN (SELECT ID FROM SecondaryTable2)

Open in new window

0
 

Author Comment

by:JCTDD
ID: 35065514
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 35065614
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:JCTDD
ID: 35065638
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
 
LVL 9

Expert Comment

by:kaminda
ID: 35065653
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 35065673
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
 

Author Comment

by:JCTDD
ID: 35065751
I figured out the problem I manually typed the values in the secondary table instead of using INSERT

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now