The Join transform allows joining two tables by defining the keys and specifying the join type. If relationship exists between the two tables, the link is automatically created, but can be changed if necessary.
If the data connector of the transforms being joined are the same, and that provider supports joining, then, an optimized query will be generated and sent to the server.
The Join transform requires 2 input transforms.
In the Join configuration dialog, there are three steps to perform:
- Choose the Join Type (which can be Inner Join, Left Join, Right Join, or Full Join).
- Drag and drop key column. Look at the two lists of database table columns. Each list corresponds to one of the inputs to the Join transform. To form a unique key binding for the join operation, drag a column from the first list and drop it over a column from the second list. For example, drag SalesOrderID from the left list and drop it onto SalesOrderID in the right list. You'll see an icon displayed beside each column that is part of the binding, and the columns will also be highlighted in color if you hover over them.
Dundas BI will set this binding for you automatically in some cases.
- Use the checkboxes to select or de-select columns for inclusion in the output. For example, select SalesOrderID, OrderQty, ProductID, and ModifiedDate from the left list. Select SalesOrderID and SalesPersonID from the right list.
The figure below illustrates the output from the Join transform.