Solved

Help with a SQL Statement

Posted on 2011-09-18
6
156 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
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:pivar
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

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:csimmons1324
Comment Utility
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
Comment Utility
>>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
Comment Utility
Both acperkins and my solution seem to work.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

10 Experts available now in Live!

Get 1:1 Help Now