SQL Join on multiple columns?

100 pts.
Tags:
AS/400
JOIN statement
SQL
Can I join from one file to another on more than one column? For example, join from FileA to FileB on OrderNumber.A=OrderNumber.B AND ItemNumber.A=ItemNumber.B? All examples show only one column... I am on an AS400 using RPGLESQL, I need to join from one detail file to another detail file.

Software/Hardware used:
AS400, RPGLESQL,PkMS

Answer Wiki

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

Yes, you can. You have it coded correctly by just using the AND.

Here is a sample of my code:
This is in a source member as I try to use RUNSQLSTM.
That way I can make modifications without having to recompile programs.
====
INSERT INTO CUBRANCH
(CUNO, ORGBRN, ORGNAM, BRNAME, USRPRF)
SELECT ’1379′, t.ORGBRN, t.ORGNAM, t.BRNAME, ‘New Record’
FROM cuBRaNCHt t Exception Join cubranch p
on t.cuno = p.cuno
and t.oRGBRN = p.ORGBRN;

Discuss This Question: 4  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Wlakesearcher
    Thank you. I was trying this in STRSQL and it looks like I was close but I was also using an invalid field name. I assumed the error message was referring to my AND in the Join portion of the statemnt instead of the bad field name. Can you (or anyone else) recommend a good book to use for SQL? We are moving from the AS400 to Windows 2008R2 so I will need something on SQLSERVER 2008, but I will also need to support the AS400 for some time, so something on AS400 SQL? I don't even know what RUNSQLSTM is or how to use it, but it looks intriguing!
    100 pointsBadges:
    report
  • TomLiotta
    RUNSQLSTM is a command that executes SQL statements from a source member. It's handy for storing SQL that you want to save for later use. I sometimes exit STRSQL and save my session in a source member. Then I edit the member to remove all of the excess garbage, leaving just the statements that I want to keep in that member. You can run a series of statements by separating each statement with a semi-colon. Parms on the command let you specify run-time elements such as error severity -- i.e., if you expect an error on one statement, you can have it ignored and the following statements will still run. For example, I might have a CREATE TABLE that I run to create a test table. That might be followed by a series of INSERT statements to create some test data. I usually have a DROP TABLE as my first statement, but I don't want the series to stop just because the table doesn't exist and errors out when I try to DROP it. Sometimes I'll save statements and edit them to make them neatly readable. I might do that so I'll have an example of something like JOIN on multiple columns. A month later, I can open that member and see an example that makes sense to me. Tom
    125,585 pointsBadges:
    report
  • Wlakesearcher
    Thank you for the information on RUNSQLSTM. I can see how it would be very useful for saving examples, right now I take a print key , make notes on it and file it. I like your way a lot better and the examples could be shared by others in the department, too. I will also give it a try on the statement that I have in the RPGLESQL pgm so that a recompile is not necessary if the SQL statement changes.
    100 pointsBadges:
    report
  • TomLiotta
    RUNSQLSTM is limited in the SQK statements that it can run. The major limitation that confuses most new users is that a SELECT statement is not allowed. Since a SELECT is intended for defining a set of records for a program (or SQL procedure) to process, there is nothing that RUNSQLSTM can do with a SELECT. I.e., a SELECT by itself will neither display nor print the selected rows. A program must FETCH rows and route them to a display or printer. (STRSQL runs a program that knows what to do with a SELECT, so they are allowed in a STRSQL session.) If a SELECT is needed for displaying or printing rows or for routing them to an outfile, use a QM query instead. The STRQMQRY command can run SELECT statements from source members because the command-processing program knows what to do. Tom
    125,585 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following