RE: SQL join question

First copy file a to a new file, include all records.  Now you said it
is keyed unique so you can copy all of file b to this new file and allow
*NOMAX on errors, you will get a file with the combination of both files
yet no duplicate records because it is keyed unique.  If you are not
forcing the unique key, this will not work.

You also can generate a report using RPG with Input Primary / Input
Secondary printing the 4 columns as you have illustrated.  SQL does have
it's limitations.

Heck the RPG will only have input/output spec's and know C spec's.


Christopher Bipes
Information Services Director
CrossCheck, Inc.

707.586.0551, ext. 1102
707.585.5700 FAX

Chris.Bipes@xxxxxxxxxxxxxxx
www.Cross-Check.com

Notice of Confidentiality: This e-mail, and any attachments thereto, is
intended only for use by the addressee(s) named herein and may contain
legally privileged and/or confidential information.  If you are not the
intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, and any
attachments thereto, is strictly prohibited.  If you have received this
e-mail in error, please immediately notify me by e-mail (by replying to
this message) or telephone (noted above) and permanently delete the
original and any copy of any e-mail and any printout thereof.  Thank you
for your cooperation with respect to this matter.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rick baird
Sent: Friday, February 10, 2006 8: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?