Solved

Help with a SQL Statement

Posted on 2011-09-18
6
158 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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 …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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