Re: OUTER JOIN question
--------------3E509EB01DD30F74509EE9D5
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Reiner Kunz wrote:
> Hi,
>
> I have two tables of the same structure. I want to select all records from
> the second table which do not appear in the first table. How Do I do this
> with an OUTER JOIN? Or is there another way?
>
> Then I want to write the found records in a third table for further
> analysis. Many thanks for your help!
OUTER JOIN isn't what you want; and OUTER JOIN would get you the contents of
table a even if there are no joining rows in an child table (e.g. customers
with or without orders).
Assuming the two tables share a primary key that has only 1 column:
SELECT tablea.* FROM tablea WHERE tablea.pkcolumn NOT IN (SELECT pkcolumn FROM
tableb)
To put it in an existing table:
INSERT INTO tablec SELECT ...
To put in a temp table created on the fly:
SELECT ... INTO TEMP temptable
If the two table share a primary key that is more than 1 column (and check me
on syntax; I haven't done this is awhile):
SELECT tablea.* FROM tabla WHERE NOT EXISTS
(SELECT anycolumn FROM tableb where tableb.col1 = tablea.col1 AND tableb.col2
= tablea.col2 ...)
It doesn't matter what column you select from tableb (as I recall), because
we're just searching for the exsitance of a row, not any specific value.
This last is grain-of-salt level, but I'm sure NOT EXISTS will work for you
somehow.
//////////////// =======================================================
////////// // Dennis J. Pimple Informix Software, Inc.
////// / /// Principal Consultant 6300 S Syracuse Way Ste 205
///// // //// dennisp@informix.com Englewood CO 80111
//// // /////
/// // ////// recept: 303-850-0210
// // /////// direct: 303-740-5611 Opinions expressed are mine,
/ /////////// fax: 303-779-4025 and do not necessarily
//////////////// http://www.informix.com reflect those of my employer
--------------3E509EB01DD30F74509EE9D5
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit
<HTML>
<P>Reiner Kunz wrote:
<BLOCKQUOTE TYPE=CITE>Hi,
<P>I have two tables of the same structure. I want to select all records
from
<BR>the second table which do not appear in the first table. How Do I do
this
<BR>with an OUTER JOIN? Or is there another way?
<P>Then I want to write the found records in a third table for further
analysis. Many thanks for your help!</BLOCKQUOTE>
OUTER JOIN isn't what you want; and OUTER JOIN would get you the contents
of table a even if there are no joining rows in an child table (e.g. customers
with or without orders).
<P>Assuming the two tables share a primary key that has only 1 column:
<P>SELECT tablea.* FROM tablea WHERE tablea.pkcolumn NOT IN (SELECT pkcolumn
FROM tableb)
<P>To put it in an existing table:
<BR>INSERT INTO tablec SELECT ...
<P>To put in a temp table created on the fly:
<BR>SELECT ... INTO TEMP temptable
<P>If the two table share a primary key that is more than 1 column (and
check me on syntax; I haven't done this is awhile):
<P>SELECT tablea.* FROM tabla WHERE NOT EXISTS
<BR>(SELECT anycolumn FROM tableb where tableb.col1 = tablea.col1 AND tableb.col2
= tablea.col2 ...)
<P>It doesn't matter what column you select from tableb (as I recall),
because we're just searching for the exsitance of a row, not any specific
value.
<P>This last is grain-of-salt level, but I'm sure NOT EXISTS will work
for you somehow.
<BR>
<P><TT>//////////////// =======================================================</TT>
<BR><TT>////////// // Dennis J. Pimple
Informix Software, Inc.</TT>
<BR><TT>////// / /// Principal Consultant
6300 S Syracuse Way Ste 205</TT>
<BR><TT>///// // //// dennisp@informix.com
Englewood CO 80111</TT>
<BR><TT>//// // /////</TT>
<BR><TT>/// // ////// recept: 303-850-0210</TT>
<BR><TT>// // /////// direct: 303-740-5611
Opinions expressed are mine,</TT>
<BR><TT>/ /////////// fax: 303-779-4025
and do not necessarily</TT>
<BR><TT>//////////////// <A HREF="http://www.informix.com">http://www.informix.com</A> reflect
those of my employer</TT>
<BR><TT> </TT></HTML>
--------------3E509EB01DD30F74509EE9D5--