Prev: delete rows from a large table with text column
Next: Not allow multiples in same month
From: Bruno on 3 Mar 2010 12:35
Hi All,

Supose I have a Products table with code and description fields:

cod description
--- -----------
001 hamburger
002 cheesburger
003 hotdog

In this particular case, duplicate codes are not a problem since the
code is not a primary key,and similar items stocked at various
facilities will have the same code, with another field being used to
determine the specific stock item's location. So this looks more like
the actual table:

code loc description
---- --- -----------
001 A hamburger
002 A cheesburger
003 A hotdog
001 B hamburger
002 B cheesburger
003 B hotdog

Now suppose that someone at site B types different descriptions (for
instance, "burger" instead of "hamburger" or "hot-dog" instead of
"hotdog")? How can I select ONLY those items that, while having
identical codes, have different description fields? I'd like to
present a list of description conflicts across sites, so they can be
resolved. From the above, this is the desired output:

code loc description
---- --- -----------
001 A hamburger
001 B burger
003 A hotdog
003 B hot-dog


Thanks for any ideas,
Bruno.
From: Tom Cooper on 3 Mar 2010 12:53
One way

Select p.cod, p.loc, p.description
From Products p
Inner Join (Select p1.cod, Count(Distinct p1.description
From Products p1
Group By p1.cod
Having Count(Distinct p1.description) > 1);

Of course, the best solution to this sort of problem is to have a normalized
database, something like

Create Table Products (cod int not null,
description varchar(25) not null,
Constraint PKProducts Primary Key (cod),
Constraint UKProductsDescription Unique (description));

Create Table ProductLocations(cod int not null,
loc char(1) not null,
Constraint PKProductLocations Primary Key (cod, loc));

If you have a normalized database like the above, this problem can't ever
occur.

Tom

"Bruno" <brunobl(a)hotmail.com> wrote in message
news:2c7to59hu64ccsn9uboikvj6pin89vr2v8(a)4ax.com...
> Hi All,
>
> Supose I have a Products table with code and description fields:
>
> cod description
> --- -----------
> 001 hamburger
> 002 cheesburger
> 003 hotdog
>
> In this particular case, duplicate codes are not a problem since the
> code is not a primary key,and similar items stocked at various
> facilities will have the same code, with another field being used to
> determine the specific stock item's location. So this looks more like
> the actual table:
>
> code loc description
> ---- --- -----------
> 001 A hamburger
> 002 A cheesburger
> 003 A hotdog
> 001 B hamburger
> 002 B cheesburger
> 003 B hotdog
>
> Now suppose that someone at site B types different descriptions (for
> instance, "burger" instead of "hamburger" or "hot-dog" instead of
> "hotdog")? How can I select ONLY those items that, while having
> identical codes, have different description fields? I'd like to
> present a list of description conflicts across sites, so they can be
> resolved. From the above, this is the desired output:
>
> code loc description
> ---- --- -----------
> 001 A hamburger
> 001 B burger
> 003 A hotdog
> 003 B hot-dog
>
>
> Thanks for any ideas,
> Bruno.

From: Plamen Ratchev on 3 Mar 2010 13:01
Try this:

SELECT code, loc, description
FROM (
SELECT code, loc, description,
COUNT(*) OVER (PARTITION BY code, description) AS cnt
FROM Codes) AS C
WHERE cnt = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Bruno on 3 Mar 2010 15:11
Tom,

First, thanks so much for putting your time into this.

>One way
>
>Select p.cod, p.loc, p.description
>From Products p
>Inner Join (Select p1.cod, Count(Distinct p1.description
> From Products p1
> Group By p1.cod
> Having Count(Distinct p1.description) > 1);

I can't get that to run. I see there is a parethesis mismatch
somewhere but cannot for the life of me find the right configuration.

I'd be VERY grateful if you could spare yet some more time and help me
out trying to get this example to run.

Thanks for the normalizing heads-up. This is a cross-platform, 24yrs+
old system with clipper/DBF & SQL ends. Pretty interesting stuff.

Thanks again,
Bruno.
From: Bruno on 3 Mar 2010 15:35
Hi Plamen,
Thanks for helping!

>Try this:
>
>SELECT code, loc, description
>FROM (
>SELECT code, loc, description,
> COUNT(*) OVER (PARTITION BY code, description) AS cnt
>FROM Codes) AS C
>WHERE cnt = 1;

Your example program does return the records with different
descriptions for the same code, but interestingly, some of the codes
returned refer only to one of the locations.

For example, for a sample like this

code loc description
---- --- -----------
001 A hamburger
002 A cheesburger
003 A hotdog
001 B burger
002 B cheesburger
003 B hot-dog

The result dataset might be, for instance,

code loc description
---- --- -----------
001 A hamburger
003 A hotdog
003 B hot-dog

The burger should also be in the list as it is one of the affected
items, but is missing.

This is only an example of course, so maybe it won't reflect the exact
result you get if you are testing with those exact records. My results
were with a live database, withresults similar to the above.

I wonder if you have other ideas to further refine your suggestion?

Many, many thanks,
Bruno.
 |  Next  |  Last
Pages: 1 2
Prev: delete rows from a large table with text column
Next: Not allow multiples in same month