Re: SQL join question

never mind, got it!

for the record:

select KEY1, DESC1, KEY2, DESC2
from FILE1
left outer join FILE2
on KEY1 = KEY2
UNION
select KEY1, DESC1, KEY2, DESC2
from FILE2
exception join FILE1
on KEY1 = KEY2
order by KEY1, KEY2

thanks for letting me use this forum jar the cobwebs loose from my own brain!

Rick

On 2/10/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> 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
>