Solved

Help with a SQL Statement

Posted on 2011-09-18
6
160 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 500 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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.

696 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