You are right Joe...

I went the route of union because I assumed Ash wanted records from both
tables. From what I can see, if you join the two tables, you will miss the
records that are unique in the joined table.

So if table 1 has 1 record and table 2 has 3. And you join table 2 to
table 1, your result will only be 1 record.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"


java400-l-bounces@xxxxxxxxxxxx wrote on 11/05/2007 11:37:07 AM:

From: Joe Pluta

SELECT t1.id, t1.type, COALESCE(t2.value, t1.value)
FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id and t1.type = t2.type

That ought to do the trick. Basically, if there is a row in T2,
t2.value
will be used. Otherwise, t2.value will be NULL and t1.value will be
used.

Nothing against Michael's UNION technique, either, this technique just
seems
a little more straightforward to me. But that's the thing about SQL;
there
are often several ways to do the same thing, and the performance of each
technique may be influenced by the data in the files.

Joe

--
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2021 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.