RE: SQL join question

Rick,

You can combine a left outer join and an exception join with a union.

select key1,description1,key2,description2
from file1
left outer join file2
on key1=key2
UNION
select ' ',' ',key2,description2
from file2
exception join file1
on key2=key1

The left outer join will give you all the records in file1 along with any
matching records in file2. The exception join will give you the records in
file2 that didn't have a match in file1.

Hope this helps!
Richard



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