Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Find same product with dissimilar description Ids

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
Smittles
Asked:
Smittles
  • 15
  • 12
1 Solution
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SmittlesAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
Cool, let me test this out... thanks for your help so far.
0
 
SmittlesAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
Okay, one minute...
0
 
SmittlesAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
@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
 
SmittlesAuthor Commented:
Can I select into a temp table from this?
0
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
I'll check on this tomorrow.. Thanks for you help so far.
0
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
Yeah, that's what I want. :-) K, lemme give this a whirl on the development machine...
0
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
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
 
SmittlesAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
which query?
0
 
HainKurtSr. System AnalystCommented:
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
 
SmittlesAuthor Commented:
This one: select *
into descriptions_temp
from (select productid, min(descriptionid) from descriptions where descriptionid>1 group by productid)
0
 
SmittlesAuthor Commented:
2 months and no firm answers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 15
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now