× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks for the reply Mike
Wow
Many things to Pinder and play with



Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone


-------- Original message --------
From: Mike Jones <mike.jones.sysdev@xxxxxxxxx>
Date: 6/30/17 5:32 PM (GMT-05:00)
To: midrange-l@xxxxxxxxxxxx
Subject: 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


--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2024 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.