 |
|||||
|
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 |
| [SOLVED] Joining 3 tables | Login/Join |
| Gold member |
Hi All, I have 3 tables A, B and C A has 500 records. B has 300 records. C has 100 records. there is a common field xyz in these tables. when inner join on B and C, gives 50 records. where every record of either B or C is present in A. Now I want a join,(A should be the hostfile), which retrieves all the records from B and all the Records from C but not A. so, as per above example A should hold 350 records. How to do this join. one way I know is, to do a Match on B and C and hold it, then do inner join on A with the Hold. Can anyone tell me a simple way of doing this.This message has been edited. Last edited by: nsk110483, In Focus since 2008 WebFOCUS 8.2.0.1 Windows 7 - IE,Chrome,Firefox Excel, PDF, HTML, AHTML, XML JavaScript, jQuery, D3.js, Highcharts | ||
|
| Virtuoso |
By definition of the join, you would never be able to get records from C that are not in A. If a record is not in A, there is no value to join to B or C. However, you may be able to us B as the host file and get all matching records from A and any additional records from C. However, MATCH FILE seems like it would be your best bet. Regards, Darin In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat | |||
|
| Virtuoso |
If you are comfortable coding SQL, you might be able to accomplish this with SQL joins via pass-thru SQL. Otherwise I believe MATCH FILE logic is the only way to accomplish the union you want between tables B and C. If you use MATCH FILE for tables B and C, then you might as well add table A and accomplish everything with one MATCH FILE. The number of records in the three tables is small enough that the use of MATCH FILE will not be a performance issue.This message has been edited. Last edited by: Dan Satchell, WebFOCUS 7.7.05 | |||
|
| Expert |
Just to clarify, are you wanting ALL records in B that have a match in A and only those in C that do not have a match in A but have a match in B? Then either of the suggestions given should work. If your source is an RDBMS then I would be inclined to use SQL passthru with UNION, but that would depend upon your skill in SQL. However, in this scenario and using your figures, if there are 50 matches between B and C and you are expecting those 50 matches to be in addition to those matched between A and B, that infers there are 50 records in B that would not match those in A - this would mean that you should only expect 250 matches between A and B giving you a total of 300 records in the final result and not 350? T
| |||||||||
|
| Gold member |
the rule is, all the records in B are in A all the records in C are in A some records in B are not in C some records in C are not in B In Focus since 2008 WebFOCUS 8.2.0.1 Windows 7 - IE,Chrome,Firefox Excel, PDF, HTML, AHTML, XML JavaScript, jQuery, D3.js, Highcharts | |||
|
| Expert |
In that case MATCH FILE B [verb required] fieldnames BY common field RUN FILE C [verb required] fieldnames BY common field AFTER MATCH HOLD AS yourfilename OLD-OR-NEW RUN FILE A [verb required] fieldnames BY common field AFTER MATCH HOLD AS yourfilename OLD-AND-NEW END T
| |||||||||
|
| Gold member |
Hi, There is a field 123 in B and 456 in C at once the report should only display by either of the two. from Launch Page, if i select 123 the report sort field is BY 123 from Launch Page, if i select 456 the report sort field is BY 456. if i do JOIN x IN A TO UNIQUE x IN B AS J0 END JOIN x IN A TO UNIQUE x IN C AS J1 END I need to know the parse of these stmts will it join the common records in A with B and then join the common records in A with C or since the reports needs to be displayed only by one element in either of those 2 tables, will it be like only one of these Joins will be active based on the selection from the LP. In Focus since 2008 WebFOCUS 8.2.0.1 Windows 7 - IE,Chrome,Firefox Excel, PDF, HTML, AHTML, XML JavaScript, jQuery, D3.js, Highcharts | |||
|
| Virtuoso |
"Activation" of JOINs depends on whether or not a column in the child/foreign table is mentioned in the query. If no columns in table C are mentioned in the query then the A -> C JOIN will not be activated - same for the A -> B JOIN. If columns from both B and C are mentioned in the query, then the outcome depends on a number of factors, including the type of data source(s) (Oracle, SQL Server, DB2, FOCUS, etc.) The only sure way to determine the result of any particular query is to test. WebFOCUS 7.7.05 | |||
|
| Gold member |
Thanks Dan, This is exactly what I wanted to know. In Focus since 2008 WebFOCUS 8.2.0.1 Windows 7 - IE,Chrome,Firefox Excel, PDF, HTML, AHTML, XML JavaScript, jQuery, D3.js, Highcharts | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| 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 |