?
Solved

Help with a SQL Statement

Posted on 2011-09-18
6
Medium Priority
?
161 Views
Last Modified: 2012-05-12
This should be rather simple but for some reason I cannot figure this out.  I have two tables in my database.  The first is Vendor_Price_List and the other is All_Products.  In both tables is a field called dc_custom_upc.  I want to find the records within the Vendor_Price_List table that has a value in the dc_custom_upc field that does not exist in the All_Products table.

I have attached my code for review.  What is confusing me is that that I change dc_custom_upc to another field, such as name or sku, it returns records.  I have tested this with one record in the Vendor_Price_List table with a dc_custom_upc that I know for a fact does not exist in the other table.


SELECT *
 FROM dbo.Vendor_Price_List
 WHERE "dc_custom_upc" not in (SELECT "dc_custom_upc" FROM dbo.All_Products);

Open in new window

0
Comment
Question by:csimmons1324
[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
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36557304
Hi,

Try


SELECT *
 FROM dbo.Vendor_Price_List vpl
 WHERE vpl.dc_custom_upc not in (SELECT ap.dc_custom_upc FROM dbo.All_Products ap);

/peter
0
 

Accepted Solution

by:
csimmons1324 earned 0 total points
ID: 36557611
I was able to resolve the problem by using the following:

SELECT *
 FROM dbo.Vendor_Price_List
 WHERE "dc_custom_upc" not in (SELECT "dc_custom_upc" FROM dbo.All_Products WHERE "dc_custom_upc" is not NULL);
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 2000 total points
ID: 36557734
This is the correct approach:
SELECT	vpl.*
FROM	dbo.Vendor_Price_List vpl
	LEFT JOIN dbo.All_Products ap ON vpl.dc_custom_upc = ap.dc_custom_upc
WHERE	ap.dc_custom_upc IS NOT NULL

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:csimmons1324
ID: 36582541
acperkins,

Will your method provide different results then the one that I used or is just a cleaner approach?  In the limited testing that I have done, the approach that I took seems to be giving me the proper results.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36583907
>>In the limited testing that I have done, the approach that I took seems to be giving me the proper results. <<
Then go with that.  I just thought you wanted something better.
0
 

Author Closing Comment

by:csimmons1324
ID: 36895872
Both acperkins and my solution seem to work.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 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