Re: Using SQl to obtain a field value from one file or another

Hard to say on INNER JOIN vs. LEFT JOIN, as I didn't fully study all the
back and forth on this thread to see if we had sufficient information to
make that determination.

If the rows being joined are ALWAYS present in ALL of the tables, then an
INNER JOIN is likely what Alan wants. If the rows are present only some of
the time in some of the tables, then a LEFT JOIN is likely what Alan
wants. However, it really depends on the requirements. If rows are
present only some of the time in some of the tables, but he only wants to
return the subset of rows in which the rows are always present across all
the tables, then an INNER JOIN is what Alan wants. If rows are present
only some of the time in some of the tables, and not a single table
contains all the row keys he wants to deliver, and he wants to return all
possible key rows across all the tables, then he wants a FULL OUTER JOIN.

If the keys used to join the tables are not unique, and he needs logic to
only pull in one row (out of many) from a joined table, then he needs to
use a INNER JOIN LATERAL or LEFT JOIN LATERAL to a sub-query that contains
an ORDER BY and FETCH FIRST ROW ONLY clauses, in order to prevent the
result set from expanding to more rows than desired. Alternatively, common
table expressions using the ROW_NUMBER() function can easily do the same
thing.

So, the old cliche "it depends" applies...

Mike

date: Fri, 30 Jun 2017 16:54:48 -0400
from: Rob Berendt <rob@xxxxxxxxx>
subject: Re: Using SQl to obtain a field value from one file or
another

So, if he uses left outer join (or just left join) he'll have to do all
that null checking stuff. However if he uses left inner join he should be
good?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com