Re: SQL join question

Richard, Eric and Doug,

Thanks! that's it exactly.

Chris, I WOULD have used matching records, if only to mess with the
anti-cycle contingency out there :-)   but I need to randomly access
the records, and don't want another intermediate file.

thanks to all,

Rick

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