RE: SQL join question

Hi Rick,

What you're talking about is called a "Full Join", and even though it's not
directly supported in OS400 (it might be available in newer OS version, not
really sure), you can easily do this with a union of a left outer join and
an exception join....

Select  a.*, b.* 
>From           file1   a       left outer join
                file2   b       on (a.key1=b.key1)
union 
Select  a.*, b.*
From            file2 b exception join
                file1 a on (a.key1=b.key1)

Note: Left outer join will return all rows from file1, whether or not
there's a match in file2.  All that's left is any entries in table two that
do not have matches in table1.  That's why we union the exception join...
Notice that the select always stipulates fields from a, then b.  This is to
ensure that the columns retain the correct position in the resulting
output.....


Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rick baird
Sent: Friday, February 10, 2006 10:53 AM
To: Midrange Systems Technical Discussion
Subject: SQL join question


I have two identical files, keyed uniquely by the same fields.

the first will have records that may or may not exist in the second
and the second will also contain records that may or may not exist in
the first.

I need an sql select statement (or DDS join if that's my only option)
that will pair these records side by side, showing all records in both
files, like this:

contents of file1

key1 description1
a    description
b    description
c    description
d    description
e    description

contents of file2

key2 description2
c    description
d    description
e    description
f    description
g    description

result of select - join

key1 description1    key2 description2
a    description     -    -
b    description     -    -
c    description     c    description
d    description     d    description
e    description     e    description
-    -               f    description
-    -               g    description

a left outer join will not include the f or g records in the second
file, and an exception join won't show the c,d,e records in either
file.

what's a mother to do?

thanks,

Rick