Solved

SQL excluding IDs

Posted on 2011-03-07
8
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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