RE: SQL join question

Not quite ( although I must admit that was my first thought till I
checked in the manual :-)

An exception join can be right or left only, just like an outer join.
Saying EXCEPTION JOIN is the same as saying LEFT EXCEPTION join just
like saying OUTER JOIN is the same as saying LEFT OUTER JOIN.  A right
exception join would only give f,g and a left exception join would give
a,b.

Thus the OP needs to use a left out join to get a,b,c,d,e and a right
exception join to get f,g.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> Doug_McLauchlan@xxxxxxxxxx
> Sent: Friday, February 10, 2006 12:13 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL join question
> 
> I'm just guessing here - never been tried by me -  but would 
> a UNION of a 
> JOIN and an EXCEPTION JOIN work ?
> 
> The JOIN gets you c, d & e and the EXCEPTION JOIN gets you 
> the others. Put 
> them together with a UNION and hey presto ? 
> 
> 
> Doug McLauchlan
> BPCS Support
> Planar Systems, Inc.
> Office: (503) 748-5988
> Cell: (503) 706-7525
> 
> 
> 
> rick baird <rick.baird@xxxxxxxxx> 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 02/10/2006 08:52 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> 
> To
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> cc
> 
> 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
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> ______________________________________________________________________
> This e-mail, and any attachments, is intended only for use by the 
> addressee(s) named herein and may contain legally privileged and/or 
> confidential information.  It is the property of Planar 
> Systems, INC.  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, any 
> attachments thereto, and use of the information contained, is 
> strictly 
> prohibited.  If you have received this e-mail in error, 
> please notify the 
> sender and permanently delete the original and any copy thereof.
> ______________________________________________________________________
> 
> 
> 
> ______________________________________________________________________
> This e-mail, and any attachments, is intended only for use by 
> the addressee(s) named herein and may contain legally 
> privileged and/or confidential information.  It is the 
> property of Planar Systems, INC.  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, any 
> attachments thereto, and use of the information contained, is 
> strictly prohibited.  If you have received this e-mail in 
> error, please notify the sender and permanently delete the 
> original and any copy thereof.
> ______________________________________________________________________
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>