Creating a table analysis with an SQL business rule with a join condition
About this task
In some cases, you may need to analyze database tables or views using a SQL business rule that has a join clause that combines records from two tables in a database. This join clause will compare common values between two columns and give a result dataset. Then the data in this set will be analyzed against the business rule.
Depending on the analyzed data and the join clause itself, several different results of the join are possible, for example #match + #no match > #row count, #match + #no match < #row count or #match + #no match = #row count.
The example below explains in detail the case where the dataset in the join result is bigger than the row count (#match + #no match > #row count) which indicates duplicates in the processed data.
- At least one SQL business rule has been created in the Profiling perspective of Talend Studio.
- At least one database connection is set in the Profiling perspective of Talend Studio.
In this example, you want to add the SQL business rule created in Creating an SQL business rule to a Person table that contains the age and name columns. This SQL business rule will match the customer ages to define those who are older than 18. The business rule also has a join condition that compares the "name" value between the Person table and another table called Person_Ref through analyzing a common column called name.
![Example of the tables used in the procedure.](/en-US/studio-user-guide/8.0-R2024-07/Content/Resources/images/table_join_condition.png)
![Results of the join condition between the two tables.](/en-US/studio-user-guide/8.0-R2024-07/Content/Resources/images/table_join_condition_result.png)
The result set may give duplicate rows as it is the case here. Thus the results of the analysis may become a bit harder to understand. The analysis here will not analyze the rows of the table that match the business rule but it will run on the result set given by the business rule.