Solved

SQL excluding IDs

Posted on 2011-03-07
8
273 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
Industry Leaders: 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!

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2005 - Memory Table Column Names 11 81
SQL query to summarize items per month 5 79
Email Notifications for SQL 2005 9 38
SQL- GROUP BY 4 25
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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