Re: Joining two files on multiple columns?

["Steve Landess" <steve_landess@xxxxxxxxxxx>]

> Reeve wrote:
> I'm stumped: what's the SQL syntax for joining two files on multiple
columns
> (joining both "city" and "state")?  I can do in Oracle SQL but not in
> OS/400, and there doesn't appear to be a single example of doing this in
any
> IBM manual.
>
> Thanks,
> Reeve

Reeve,

Here is another way (using left outer join):

select *From
sjl001/cusmst aa
left outer join sjl002/cusmst bb
on  aa.cunbr = bb.cunbr
and aa.culnam = bb.culnam

and another (using inner join):

select *From
sjl001/cusmst aa
inner join sjl002/cusmst bb
on  aa.cunbr = bb.cunbr
and aa.culnam = bb.culnam

and another (using a correlated join):

select *From
sjl001/cusmst aa  where exists
(select *  from sjl002/cusmst bb
where  aa.cunbr = bb.cunbr
and    aa.culnam = bb.culnam)

and here's an exception join -
(unmatched records - shows records in SJL002/CUSMST
 that *don't* exist in SJL001/CUSMST, based on joining the
 two fields CUNBR and CULNAM):

select *From
sjl002/cusmst aa
exception join sjl001/cusmst bb
on  aa.cunbr = bb.cunbr
and aa.culnam = bb.culnam

Finally -
Try looking here for more information (in the info center,
under Database and file systems>DB2 UDB for iSeries>Examples):

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/rzahf/rzahfex0.htm

Scroll down until you see the section on Joins...

Hope this helps,

Steve Landess
Austin, Texas
(512) 423-0935