• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Help with a SQL Statement

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
csimmons1324
Asked:
csimmons1324
  • 3
  • 2
2 Solutions
 
pivarCommented:
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
 
csimmons1324Author Commented:
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
 
Anthony PerkinsCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
csimmons1324Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
csimmons1324Author Commented:
Both acperkins and my solution seem to work.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now