I've got a production database where a description ID is automatically generated in another table, and joined by the product ID. Before we got this automated, there were cases where the Product ID was entered by hand, and sometimes cases occur where our vendors see duplicate entries on their control panel in our CMS, due to the products being retrieved in a DISTINCT clause in the SELECT statement. All details are identical except this description ID (even the descriptions are the same!). I want to find which products are affected by this, and make sure the ProductIds match now, for all products.
So, I have two tables: Product and Description
Relevant columns on the tables are
Product.ProductId , Product.Name, Description.ProductId, Description.DescriptionID
I want to obtain a list of affected Products by their product ID, then set the ones that are different to the lowest integer of the collected description Ids, so long as the product IDs are exact matches.
Both ProductId and DescriptionId are Integers (thankfully).
I've attached the code that I attempted, but I get zero results.
SELECT Product.ProductId, Description.DescriptionId, Description.Description
JOIN Description ON Product.ProductId = Description.ProductId
WHERE Product.ProductId = Product.ProductId
AND Description.Description = Description.Description
AND DescriptionId <> DescriptionId