Handling multiple JOINs through Step by Step Procedure to create a dataset

Modified on Thu, 14 Mar at 12:29 PM

Requirement:

Handling multiple JOINs through Step by Step wizard to create dataset.

e.g. Join LedgerMaster with InventoryDetail, Accounts, GoDown and its Voucher Details in AllVouchers to get the Company wise Inventory, Ledgers, Accounts, GoDown and its Voucher details.

The 6 tables used for this requirement are; Ledger master, All Vouchers, Inventory details, Godown Header, Inventory Header and Account Header as given below:

The advantage of using step-by-step wizard is that we simply need to select the tables and we can visualize the joins performed and can see the result as shown in the figure below. When we select tables, Smarten automatically detects the JOIN (if the AUTO mode is ON) with the matched columns between the tables. For e.g. when you select the Ledger Master and Voucher table, Smarten automatically detects INNER JOIN between the tables.

We have achieved the output by making multiple joins as below.

Table 1	Table 2	Type of JOIN
Ledger Master	Inventory Detail	INNER JOIN
Ledger Master	Account H	LEFT JOIN
Ledger Master	All Vouchers	INNER JOIN
Inventory Detail	Inventory H	RIGHT JOIN
Inventory Detail	Godown H	LEFT JOIN

An INNER JOIN has been performed between Ledger Master and Vouchers & Inventory Detail table. Also, we have performed the LEFT JOIN of Account Header with Ledger Master. Similarly, we have performed LEFT JOIN of GodownH with InventoryDetail. InventoryH has made a RIGHT JOIN with the InventoryDetail.

The matched joined columns can be seen if you click on the JOIN as shown in the below figure. However, user can manually delete and add joins as per their requirement.

The final output will be the dataset having Company wise Inventory, Ledgers, Accounts, GoDown and its Voucher details as shown below.

Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.

joins Smarten Step by step Multiple Joins