Solved

Find same product with dissimilar description Ids

Posted on 2011-02-10
27
179 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
[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
  • 15
  • 12
27 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34864061
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34864068
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
ID: 34864298
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:Smittles
ID: 34864309
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34864817
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
ID: 34864896
Cool, let me test this out... thanks for your help so far.
0
 
LVL 4

Author Comment

by:Smittles
ID: 34865225
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34865679
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
ID: 34865714
Okay, one minute...
0
 
LVL 4

Author Comment

by:Smittles
ID: 34866292
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34866623
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
ID: 34866814
@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
ID: 34866818
Can I select into a temp table from this?
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34867410
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
ID: 34867434
I'll check on this tomorrow.. Thanks for you help so far.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34868303
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
ID: 34872443
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34873194
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34873219
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
ID: 34873509
Yeah, that's what I want. :-) K, lemme give this a whirl on the development machine...
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34873792
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
ID: 34873885
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
ID: 34874120
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 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34875309
which query?
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34875333
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
ID: 34875743
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
ID: 35373026
2 months and no firm answers.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 31
SQL Query help 3 36
T-SQL: Wrong Result 7 32
Access 2003, find all instances of database ODBC 3 36
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore 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.

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