Left Join

The Left Join returns all the records from the left table (Bene Data), and the matching records from the right table (Tran Summary Data). The result is 0 records from the right side, if there is no match. For example, BENE_ACC_NO is the specified column in both DEMO_BENE_DATA table and DEMO_CT_TRAN_SUMMARY_DATA table as in the following screen shots:

Bene Data Table:

Open

Tran Summary Data Table:

Open

The following screenshot shows a report, Beneficiary Report created by combining Bene Data and Tran Summary Data data sources using Left join operation:

Perform the following steps in Sigma Studio to combine data sources using LEFT JOIN option:

1. Click Data Builder.
   
   

   
   The Data Builder menu option appears only when the CONNECTIONS on the left navigation is selected.
   The Data Builder screen appears.
   
   

    

2. Enter the Name and ID of the data source aggregator in the Name and ID fields. Here, DATA_BUILDER_JOINS is used as the name and ID for data source aggregator.
   
   

    

3. Select Parent Data source from Data Source ID drop-down field, which appears on the right-hand side. For example, BENE_DS2 is chosen as the data source.
   
   

    

4. Drag and drop another data from the left-hand side and connect the parent data source with this data.
   
   

    

5. Click the data element and select data source ID from the Data Source ID drop-down field. For example, TRAN_SUMMARY_DS is chosen as the data source.
   
   

    

6. Click Join.
   
   

    

7. Select any one of the Join options. Here, Left Join is selected.
   
   NOTE:
   The LEFT JOIN returns all the records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
   
   

    

8. In the Join On Keys section, select the Parent and Child columns from the respective Parent Column and Child Column lookup fields.
   
   

   
   
   

9. Click the Save to save the data source aggregator.
   
   

   
   A confirmation message appears.
   
   

10. Click Ok.

Now, proceed with the  report configuration.


On using the Left Join option in a data source aggregator, all entries on the left data source and matching records from the right data source are displayed based on the key column. For example, consider the report shown on the following screenshot. Here, BENE_ ACC_NO 10001000233 & 10001000232 is the additional entry on BENE data source. However, all entries from the BENE DATA (left table) data source and matching records from the TRAN SUMMARY DATA (right table) data source are displayed based on the key column (BENE_ACC_NO).