show / hide menu

Search Control – Inner Join

The INNER JOIN selects all rows from both tables as long as there is a match between the columns specified in the relationship tables. In the below example, we will be joining two SharePoint lists namely, Customers & Orders in order to view the orders for the particular customers. Below are the steps to achieve that.

Step 1: Drag a search control into the form and click ‘edit the datasource’ from the properties tab.

Figure 1

Step 2: Select two tables from the available tables (Customers & Orders).

Figure 2

Step 3: Select the required columns from both SQL tables.

Figure 3

Step 4: Add a search criteria to fill the search control.

Figure 4

Step 5: Select parent table name, child table name and their corresponding columns in which the inner join is required.
                      Select ‘Customer ID’ from customer list and select the same from Orders list to perform inner join, whereas both the columns are of ‘Numeric’ data types.

Note: The INNER JOIN selects all rows from both tables as long as there is a match between the columns specified in the relationship tables.

Figure 5: Customers & Orders SQL tables
Figure 6

Step 6: Next page will load all the columns in search control and click ‘Next’ button.Add ‘Formload’ trigger to fill search control and click ‘Finish’.

Figure 7

Step 7: Click ‘Preview’, you shall get the output like the one below, in which the two SharePoint lists will be joined together based on the customer ID.

Figure 8