Re: SQL Union Join?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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/rbafysimulatefoj.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.