Solved

Find same product with dissimilar description Ids

Posted on 2011-02-10
27
174 Views
Last Modified: 2013-11-05
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, Description.Description.

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.

Help! Thanks!

SELECT Product.ProductId, Description.DescriptionId, Description.Description
FROM Product
JOIN Description ON  Product.ProductId = Description.ProductId
WHERE Product.ProductId = Product.ProductId
AND Description.Description = Description.Description
AND DescriptionId <> DescriptionId

Open in new window

0
Comment
Question by:Smittles
  • 15
  • 12
27 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
this gives you Description with min descriptionID per Product

create view min_Description as
select * from (
select row_number() over (partition by ProductId  order by DescriptionID) rn, * from Products
) x where rn=1

now

select * from
products p left join on min_Description  md on p.ProductID=md.ProductID
order by p.ProductID

is what you want, just select the columns that you want...
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
oops, above should read as:

create view min_Description as
select * from (
select row_number() over (partition by ProductId  order by DescriptionID) rn, * from Description
) x where rn=1

now

select * from
products p left join on min_Description  md on p.ProductID=md.ProductID
order by p.ProductID

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
CREATE view min_Description AS
SELECT * FROM (
                    select row_number() over (partition by ProductId  order by DescriptionID) rn, * from Description
                        )
 x WHERE rn=1

-- then, do :

SELECT md.DescriptionId, * FROM
products p LEFT JOIN min_Description md ON p.ProductID=md.ProductID
ORDER BY p.ProductID

-------

Yes, this works, actually quite well, but one thing... there are sometimes default DescriptionIds that used to get -1, 0, and 1 as their value. I don't want any of those to come back - sorry I didn't bring it up sooner. Does that change the view, or the select statement after it?

Thanks!
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
And also, I only want doubles, triples or more - I don't want results where there's only one entry, and I'm getting those.

Thanks.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
change your view and add those filters (I am not sure but adding "DescriptionID > 1" looks ok)

CREATE view min_Description AS
SELECT * FROM (
                    select row_number() over (partition by ProductId  order by DescriptionID) rn, * from Description
where DescriptionID > 1
                        )
 x WHERE rn=1

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Cool, let me test this out... thanks for your help so far.
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Well, for a lot of the results, I'm not seeing multiples. I only want to see multiples, where the DescriptionId is different. Some of these are coming back with only one entry, and obviously only one DescriptionId...

Also, how do I do the update once I've got my 1000+ long list of ProductIds that I can update?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
I could not get what you are trying to do now...

please give a sample for both tables, and show what do you want to update to what value...
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Okay, one minute...
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Between these tables, the Description ID was hand-entered by an idiot or two (modifiedby) , and now must be updated, because our CMS uses a DISTINCT clause in the SELECT statement to populate search results for customers.

Because there are distinct Description IDs, which are absolutely necessary in this case, multiple results come back for the same product.

The CMS calls a stored procedure that does, roughly:

SELECT DISTINCT *, d.DescriptionID
FROM Product p
JOIN Description d
ON ProductId
WHERE (
p.ProductName LIKE '%' + @SearchPhrase + '%'
OR p.ProductID LIKE '%' + @SearchPhrase + '%'
OR p.LegacyProductID LIKE '%' + @SearchPhrase + '%'
OR p.VendorID LIKE '%' + @SearchPhrase + '%'
 )

I want to find all the cases where the ProductID has distinct DescriptionIDs.
After that, I want to update the DescriptionID based on the criteria:

MIN(DescriptionId) when greater than 1

I want to know how many have a Description ID of 1, -1, or 0, and I want the full list of Product Ids and Legacy Product Ids before I start, so I can check my work.

There are thousands of potential product cases like this.

+------------------+
|Table: dbo.Product|
+---------+--------+------+----------------+--------+----------+---------+
|ProductId|LegacyProductID|   ProductName  |VendorID|DateAdded |Exclusive|
+---------+---------------+----------------+--------+----------+---------+
|12345    | L55566        | Rubber chicken | 71     | 1/2/2005 | 1       | 
+---------+---------------+----------------+--------+----------+---------+
|12345    | L55566        | Rubber chicken | 71     | 1/2/2005 | 1       | 
+---------+---------------+----------------+--------+----------+---------+
|12345    | L55566        | Rubber chicken | 71     | 4/2/2005 | 1       | 
+---------+---------------+----------------+--------+----------+---------+
|42345    | X39993        | Magic Potion   | 88     | 4/2/2005 | 0       | 
+---------+---------------+----------------+--------+----------+---------+
|42345    | X39993        | Magic Potion   | 88     | 4/2/2005 | 0       | 
+---------+---------------+----------------+--------+----------+---------+
|96541    | E99011        | X-Ray Specs    | 71     | 2/2/2005 | 0       | 
+---------+---------------+----------------+--------+----------+---------+
|96541    | E99011        | X-Ray Specs    | 71     | 2/2/2005 | 0       | 
+---------+---------------+----------------+--------+----------+---------+
|96541    | E99011        | X-Ray Specs    | 71     | 2/2/2005 | 0       | 
+---------+---------------+----------------+--------+----------+---------+

+----------------------+
|Table: dbo.Description|
+---------+------------+--+----------------+--------+----------+
|ProductId|Description ID |   Description  |Color	|Modifiedby|
+---------+---------------+----------------+--------+----------+
|12345    | 17            | A Laff Riot    | Yellow | 135      | 
+---------+---------------+----------------+--------+----------+
|12345    | 33            | A Laff Riot    | 71     | 135      | 
+---------+---------------+----------------+--------+----------+
|12345    | 655           | A Laff Riot    | 71     | 135      |
+---------+---------------+----------------+--------+----------+
|42345    | 45            | Make her yours | 88     | 135      | 
+---------+---------------+----------------+--------+----------+
|42345    | 31            | Make her yours | 88     | 127      |
+---------+---------------+----------------+--------+----------+
|96541    | 1             | See thru stuff | 71     | 127      | 
+---------+---------------+----------------+--------+----------+
|96541    | 1             | See thru stuff | 71     | 127      | 
+---------+---------------+----------------+--------+----------+
|96541    | 13            | See thru stuff | 71     | 127      | 
+---------+---------------+----------------+--------+----------+

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
try this:


SELECT   *
  FROM      products p
         LEFT JOIN
            (  SELECT   productid,
                        COUNT (1) count_total,
                        SUM (CASE WHEN descriptionid = -1 THEN 1 ELSE 0 END)
                           count_minus_1,
                        SUM (CASE WHEN descriptionid = 0 THEN 1 ELSE 0 END)
                           count_0,
                        SUM (CASE WHEN descriptionid = 1 THEN 1 ELSE 0 END)
                           count_1
                 FROM   descriptions
                WHERE   descriptionid IN (-1, 0, 1)
             GROUP BY   productid
               HAVING   COUNT (1) > 1) d
         ON p.productid = d.productid

Open in new window

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
@HainKurt

I'm still getting values that are not repeated. I only want "twins" returned, where there is the same product ID but different Description IDs.

Sorry, I really wish I could fill in the blanks.
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Can I select into a temp table from this?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
try

also try qry by removing "descriptionid not IN (-1, 0, 1)"
SELECT   *
  FROM      products p
         LEFT JOIN
            (  SELECT   productid,
                        COUNT (1) count_total,
                        SUM (CASE WHEN descriptionid = -1 THEN 1 ELSE 0 END)
                           count_minus_1,
                        SUM (CASE WHEN descriptionid = 0 THEN 1 ELSE 0 END)
                           count_0,
                        SUM (CASE WHEN descriptionid = 1 THEN 1 ELSE 0 END)
                           count_1
                 FROM   descriptions
                WHERE   descriptionid not IN (-1, 0, 1)
             GROUP BY   productid
               HAVING   COUNT (1) > 1) d
         ON p.productid = d.productid

Open in new window

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
I'll check on this tomorrow.. Thanks for you help so far.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
to find all duplicate descriptions do this:

create view v_dup_descriptions
select productid, descriptionid , COUNT (1) count_total,
FROM   descriptions
group by ProductID, descriptionid
having count(1) >1
order by ProductID, descriptionid

shows all duplicate descriptions

select d.*
from descriptions d
inner join v_dup_descriptions dd on d.productid=dd.productid and d.descriptionid=dd.descriptionid

now to get rid of duplicates:

this query will give you unique productid, descriptionid which you want to get

create view v_unique_descriptions as
select ProductId, DescriptionID, Description, Color, Modifiedby
from (
select row_number() over (partition by productid, descriptionid order by Modifiedby) rn, d.*
from descriptions d
) x where rn=1

lets put this into a new table

select *
into unique_descriptions
from v_unique_descriptions

now we can rename descriptions as old and new one as description

alter table descriptions rename to descriptions_backup;
alter table unique_descriptions rename to descriptions;

now your descriptions table should have only unique descriptions no multiple productid/descritionid

then your query will work fine sinse there is no duplicates...

but to prevent this in the future we should add a unique index on description on (productid,descrittionid)

CREATE UNIQUE INDEX IDX_DESCRIPTION ON DESCRIPTION (productid, descrittionid);

hope it helps...

(still could not get what records do you want to get, what records you want to get rid of. you did not mention @ 34866292)
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
I don't want to get rid of any records, just update the Description ID so they're all uniform for the same product ID. Rubber Chickens should all get the same DescriptionID of 17, Magic Potion should all get 31, and X-Ray Specs should all get 13 (even though 1 is the lowest).

I apologize, those Colors should be NVARCHARS that say yellow, red, blue, etc.

I'll try these out in an hour or two.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
so you just want to update table as
+----------------------+
|Table: dbo.Description|

+---------+------------+--+----------------+--------+----------+
|ProductId|Description ID |   Description  |Color	|Modifiedby|
+---------+---------------+----------------+--------+----------+
|12345    | 17            | A Laff Riot    | Yellow | 135      | 
|12345    | 33            | A Laff Riot    | 71     | 135      | 
|12345    | 655           | A Laff Riot    | 71     | 135      |
|42345    | 45            | Make her yours | 88     | 135      | 
|42345    | 31            | Make her yours | 88     | 127      |
|96541    | 1             | See thru stuff | 71     | 127      | 
|96541    | 1             | See thru stuff | 71     | 127      | 
|96541    | 13            | See thru stuff | 71     | 127      | 
+---------+---------------+----------------+--------+----------+

-->

+---------+------------+--+----------------+--------+----------+
|ProductId|Description ID |   Description  |Color	|Modifiedby|
+---------+---------------+----------------+--------+----------+
|12345    | 17            | A Laff Riot    | Yellow | 135      | 
|12345    | 17            | A Laff Riot    | 71     | 135      | 
|12345    | 17            | A Laff Riot    | 71     | 135      |
|42345    | 31            | Make her yours | 88     | 135      | 
|42345    | 31            | Make her yours | 88     | 127      |
|96541    | 13            | See thru stuff | 71     | 127      | 
|96541    | 13            | See thru stuff | 71     | 127      | 
|96541    | 13            | See thru stuff | 71     | 127      | 
+---------+---------------+----------------+--------+----------+

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
if yes try this

update d1
set descriptionid=(select min(descriptionid) from description d2 where d2.productid=d1.productid and descriptionid>1)
from description d1
0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Yeah, that's what I want. :-) K, lemme give this a whirl on the development machine...
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
it may not work but if we put the result into a temp table it works

select *
into descriptions_temp
from (select productid, min(descriptionid) from descriptions where descriptionid>1 group by productid)

at this point descriptions_temp will have

ProductID DescriptionID
12345      17        
42345      31        
96541      13

then      

update d
set d.descriptionid=dt.descriptionid
from description d inner join descriptions_temp dt on d.productid=dt.productid

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
But what about all the other cases where the DescriptionIds match 100%? I don't want to dump those into the temp table.

Let's assume these rows exist in the Descriptions table as well, and avoid returning sets where ProductId = ProductId and DescriptionID = DescriptionID.  


+----------------------+
|Table: dbo.Description|
+---------+------------+--+----------------+--------+----------+
|ProductId|Description ID |   Description  |Color	|Modifiedby|
+---------+---------------+----------------+--------+----------+
|96541    | 13            | See thru stuff | Blue   | 127      | 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Blue   | 127      | 
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Red    | 127      | 
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Orange | 127      | 
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Yellow | 127      | 
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Green  | 127      | 
+---------+---------------+----------------+--------+----------+
|99666    | 13            |Fun for all ages| Pink   | 127      | 
+---------+---------------+----------------+--------+----------+

Open in new window

0
 
LVL 4

Author Comment

by:Smittles
Comment Utility
Also, I get a syntax error with that query
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
which query?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
you mean this query?

update d1
set descriptionid=(select min(descriptionid) from description d2 where d2.productid=d1.productid and d2.descriptionid>1)
from description d1
0
 
LVL 4

Accepted Solution

by:
Smittles earned 0 total points
Comment Utility
This one: select *
into descriptions_temp
from (select productid, min(descriptionid) from descriptions where descriptionid>1 group by productid)
0
 
LVL 4

Author Closing Comment

by:Smittles
Comment Utility
2 months and no firm answers.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

9 Experts available now in Live!

Get 1:1 Help Now