Re: Better way to write this query

It can be done with one step/CTE less:
With x as (Select Col1, Col2 From FirstTable
Union All
Select Col1, Col2 From SecondTable)
Select Col1, Col2, Count(*)
from x
Group By Col1, Col2
Having Count(*) > 1

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Robert
Rogerson
Sent: Dienstag, 8. Dezember 2020 17:49
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>;
darren.feaver@xxxxxx
Subject: 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

--
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