Complex SQL file join

110 pts.
Tags:
SQL
I have 2 files, F1 and F2. These files have a common key field KEY. I need to produce SQL that will result in one file that has all of 1)Records whose KEY value is the same in both F1 and F2 2)Records with a KEY value in F1 and no corresponding KEY value in F2, and 3)Records with a KEY value in F2 and no corresponding KEY value in F1. I thought this might be simple to do with the following FROM clause: FROM F1 INNER JOIN F2 ON F2.KEY = F1.KEY LEFT EXCEPTION JOIN F2 ON F2.KEY = F1.KEY RIGHT EXCEPTION JOIN F2 ON F2.KEY = F1.KEY but as you experienced sql'ers out there know, when I run RUNSQLSTM to compile this file, I receive error "SQL0212 30 41 Position 21 Duplicate table designator F2 not valid." Does anyone have any suggestions? Thanks in advance.
ASKED: January 20, 2005  2:11 PM
UPDATED: January 23, 2005  1:34 AM

Answer Wiki

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

I don’t understand why you want to do this with SQL at all. You want every possible combination, so just merge the 2 files and sort them if you want.

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.

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
  • Phipywr
    Brett, thank you. That gets me the correct set of records, one for each possible value of KEY in both files. Sorry about my poor knowledge of sql, but i'm just learning, so now i have another problem that i can't figure out. The sql you showed me gives me a file with only one field, KEY. I actually want the resultant file to have some fields out of each of files F1 and F2. How do I specify those fields so they end up in the resultant file? From: phipywr To: RickMe Date Sent: 20 Jan 2005 14:56 EST (19:56 GMT) Select distinct f1.key from f1,f2 where f1.key = f2.key or f1.key not = f2.key union select distinct f2.key from f2,f1 where f2.key not = f1.key Let me know if you think this is what you want or not. Brett From: RickMe To: phipywr Date Sent: 20 Jan 2005 14:43 EST (19:43 GMT) No, I don't want every possible combination. Example: F1 has 5 records with key values of A,B,B,B,and C. F2 has 4 records with key values B,C,D, and E. Even though the 2 files have 9 records between them, the resultant file that I want will have only 5 records: key values of A,B,C,D, and E. I could write an RPG prog to read both files and merge them into one, but if there is an SQL join that will do it, I thought it would be much simpler. Thanks for looking.
    0 pointsBadges:
    report
  • Phipywr
    Brett, thank you. That gets me the correct set of records, one for each possible value of KEY in both files. Sorry about my poor knowledge of sql, but i'm just learning, so now i have another problem that i can't figure out. The sql you showed me gives me a file with only one field, KEY. I actually want the resultant file to have some fields out of each of files F1 and F2. How do I specify those fields so they end up in the resultant file? From: phipywr To: RickMe Date Sent: 20 Jan 2005 14:56 EST (19:56 GMT) Select distinct f1.key from f1,f2 where f1.key = f2.key or f1.key not = f2.key union select distinct f2.key from f2,f1 where f2.key not = f1.key Let me know if you think this is what you want or not. Brett From: RickMe To: phipywr Date Sent: 20 Jan 2005 14:43 EST (19:43 GMT) No, I don't want every possible combination. Example: F1 has 5 records with key values of A,B,B,B,and C. F2 has 4 records with key values B,C,D, and E. Even though the 2 files have 9 records between them, the resultant file that I want will have only 5 records: key values of A,B,C,D, and E. I could write an RPG prog to read both files and merge them into one, but if there is an SQL join that will do it, I thought it would be much simpler. Thanks for looking.
    0 pointsBadges:
    report
  • Welcome
    Hi, You may use something like this: select f1.key from f1 inner join f2 on f1.key=f2.key union select f1.key from f1 left outer join f2 on f1.key=f2.key where f2.key is null union select f2.key from f2 left outer join f1 on f2.key=f1.key where f1.key is null If your database supports the "full join" syntax, then the above can be expressed by using one single select. In any case, you may add the additional columns you want to select, in such a way as to keep the union consistent ( same data types for corresponding selected columns ). Hope this could help. My best regards, Iudith
    0 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