Re: Better way to write this query

@darren.feaver@xxxxxx <darren.feaver@xxxxxx> , the way I think of this
is...

Table t1 would give me all items from both tables for a upc
Table t2 would give me 1 row for each upc and item
The final select would return the number of rows from t2. Therefore if the
count was > 1 then for a upc there is more than one item.

What I'm looking for is upc(s) with more than one item.

Thanks,

Rob

On Tue, Dec 8, 2020 at 10:39 AM Darren Strong <darren@xxxxxxxxx> wrote:

First you say you want to find a given UPC with differing items, but, then
you write the query to look for exact duplicates. Are you looking for
exact duplicates in addition to duplicate UPC with differing items, or just
item differences within the same UPC code?

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Robert Rogerson
Sent: Monday, December 7, 2020 7:49 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Better way to write this query

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


I have two tables. Both tables have upc and item.

I want to find if for a given upc there is more than one item in either or
both tables. So if one table has a upc with two items or both tables have
the same upc but different item then I want to return a count > 1.

I believe this query would work.

with t1 as (
select 1 as c1, 2 as c2
from sysibm.sysdummy1 a
union all
select 1 as c1, 2 as c2
from sysibm.sysdummy1 b),

t2 as (
select c1,c2
from t1
group by c1,c2)

select count(*) from t2;

Is there a better/easier way to write this query?

Thanks,

Rob
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com