Hmmmm... initially it sounded to me like he wanted something like FULL
EXCEPTION JOIN, which I don't think exists even on 6.1.
But then I looked at the examples that worked for him so far and he really
just wants a LEFT EXCEPTION JOIN with TableB as a primary dial (Michael
Schutte's last example).
Michael Schutte's 1st example showed how to simulate a FULL EXCEPTION JOIN
(except I would have used UNION ALL).
If he really wanted to look at all columns in the tables, another example,
leveraging the EXCEPT keyword would be:
SELECT * FROM TABLEA
EXCEPT
SELECT * FROM TABLEB
UNION ALL
SELECT * FROM TABLEB
EXCEPT
SELECT * FROM TABLEA
In any case, since he really needs a left exception join and is now asking
about performance, he'll just need to make sure join columns are indexed
properly.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 26, 2009 10:16 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Union Join?
Michael,
Basically, for detecting mismatches across two files what you want is
called a FULL OUTER JOIN.
Unfortunately, the i doesn't support FULL OUTER JOINs until 6.1.
However, you can get the same results as a FULL OUTER JOIN by using a
UNION of the results from LEFT OUTER and RIGHT EXCEPTION.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafysimula
tefoj.htm
(note IBM example in manual uses flips the table order and uses
EXCEPTION join, it's kinda confusing :)
select * from tableA left outer join tableB using (SKU,name, Category)
UNION ALL
select * from tableA right exception join tableB using (SKU,name, Category)
This would give you all rows from both tables:
SKU NAME CATEGORY SKU NAME CATEGORY
123 NAME1 1,001 - - -
456 NAME2 1,002 456 NAME2 1,002
123 NAME1 1,002 123 NAME1 1,002
789 NAME3 1,004 - - -
- - - 1 SOMETHING 1,002
- - - 123 NAME1 1,003
Since you only want mismatching, add a where:
select * from tableA A left outer join tableB B using (SKU,name, Category)
where b.sku is null
UNION ALL
select * from tableA A right exception join tableB B using (SKU,name,
Category)
where a.sku is null
which gives you:
SKU NAME CATEGORY SKU NAME CATEGORY
123 NAME1 1,001 - - -
789 NAME3 1,004 - - -
- - - 1 SOMETHING 1,002
- - - 123 NAME1 1,003
But it seems as if you don't want mismatches from both files, instead
what you want are only records from tableB that have a mismatched
category compared to tableA.
select * from tableB B
where exists (select 1 from tableA A
where b.sku = a.sku
and b.name = a.name
and b.category <> a.category)
and not exists (select 1 from tableA A
where b.sku = a.sku
and b.name = a.name
and b.category = a.category)
HTH,
Charles
On Thu, Mar 26, 2009 at 9:30 AM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:
If I have two tables (TABLEA, TABLEB) that have the same format, and I
want
to retrieve all the rows where a combination of two columns don't match,
would that be a union join? If so, how is that specified? Here's what I
have:
TABLEA and TABLE have the format of SKU, name and category. A SKU could
exist in multiple categories.
TABLEA
123, NAME1, 1001
456, NAME2, 1002
123, NAME1, 1002
789, NAME3, 1004
TABLEB
001, SOMETHING, 1002
123, NAME1, 1003
456, NAME2, 1002
123, NAME1, 1002
I want to retrieve 123, NAME1, 1003 because that's the one that has a
different category. I don't want to retrieve SKU 456, 789, or 001.
Hmmm...maybe I need to match on SKU with a subselect for that SKU not
having
that category?
Thanks!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
midrange.com
