 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Joins | Login/Join |
| <Edgare> |
Hi I need clarification on types of joins. Can you please provide samples of 1 to 1 join 1 to many join Left and Right Joins. I am using vms focus. Thanks! | ||
|
| Virtuoso |
1 to 1 join, means for file1 joined to file2 there is only one record in file2 for file1, or there may be none. 0 to many records in file2 for file1. When you say left and right? I only recall left, means you have an inner or outer join, if an outer then there could be a possiblility of 0 records in file2 for file1, but you want data from file1 any way. Inner, must have data in file2 for file1. Someone out there may have a nicer way of explaining. It's like saying a family can have 0 or many children, but children always have parents somewhere. Leah | |||
|
| <Edgare> |
Leah you got it all right but could you please provide the code for each type? | ||
|
| Virtuoso |
Are you using the developer tool? It handles it for you. Otherwise, I'll see what I can come up with with IB's test date. Leah | |||
|
| Virtuoso |
Note all developer studio tool generated (vs 7.6.1) Multiple JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO MULTIPLE EDUCFILE.ATTNDSEG.EMP_ID IN EDUCFILE AS J0 END Single JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO EDUCFILE.ATTNDSEG.EMP_ID IN EDUCFILE AS J0 END Inner JOIN EMPDATA.EMPDATA.PIN IN EMPDATA TO MULTIPLE EDUCFILE.ATTNDSEG.EMP_ID IN EDUCFILE AS J0 END left outer JOIN LEFT_OUTER EMPDATA.EMPDATA.PIN IN EMPDATA TO MULTIPLE EDUCFILE.ATTNDSEG.EMP_ID IN EDUCFILE AS J0 END In my world we don't add all the extras if straight code so for say single would be JOIN PIN IN EMPDATA TO EMP_ID IN EDUCFILE AS J0 END Note this would be if not a focus data base, I just deleted the tagging. Leah | |||
|
| Virtuoso |
Let me think back to when our student system was flat so to speak. We'd have student attribute records and student term records so: JOIN STU_ID IN AARTTBL TO ALL STU_ID IN RTRTTBL AS J1 END would return all records. ALL becomes MULTIPLE in the world of 7.... JOIN STU_ID IN AARTTBL TO STU_ID IN RTRTTBL AS J1 END would return first term record. Also, don't know as you can use left outer in your version. May have to use the SET ALL ON option Leah | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only Topic | View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |