RE: Suggestion with SQL query

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.