AS/400 Query Join

365 pts.
AS/400 Query
JOIN statement
I am joining four files. Each file has one field I need for my report. How do I determine which file is the primary one? Should the select records criteria all come from the primary file? As long as I test fields First>Second, Second>Third, Third>Fourth, shouldn't it work just fine?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Generally your Primary file is the one with the fewest records.
If you have multiple records in any of the files and you only want selected ones from those files, then you need Select/Omit criteria on those specific files.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    Just for discussion, without knowing anything more about the files and everything otherwise being equal, start by using the file that will provide the fewest rows as the Primary. If one file supplies 100 records and another provides 10000, based on selection criteria, there's probably no good reason to go after the second, third and fourth files more often than needed. With some useful detail info about the files and selection criteria, a better answer is probably possible. Tom
    125,585 pointsBadges:
  • lorit
    Here is a breakdown of the files: File A - has a comment field I need and I can use this file for the record selection criteria File B - has a district code I need File C - has the name field. This file has a common field with File B File D - has an amount field. With that scenario, would I just file C or D as the primary? Link those two together and the other two together? By link, I mean perform the test. Thanks :)
    365 pointsBadges:
  • Meandyou
    Lorit, Which one is the *primary file* depends on ... > you mention *query*, so I assume this is an RDBMS > *primary* defined as which table to search first in this one particular query; then I agree with the others that the one with the fewest records is a good starting point. > *primary* defined as the *master* or *parent* then you need to look at the system design and the PK / FK relationships to determine the so called hierarchy. Lorit, you named the columns you wish returned, but did not show us the join columns which most likely are the PKs and FKs.
    5,220 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: