Data Table Joins
Data Table Joins in Sequentum Enterprise are used to combine data from two or more Web Element Lists or Data Lists, based on a related column between them.
Data Table Joins work similar to a JOIN clause in relational databases.
There are four Data Table Joins in Sequentum Enterprise
Inner Join
Left Join
Right Join
Full Join
However, not all of them are supported depending on a type of your Internal Database. For example, SQLite supports Inner Join and Left Join only, while MS SQL Server supports all four of them.
Now, let's look at some data to explain how Data Table Joins work.
We have a table called Suppliers with two fields (supplier_id and supplier_name). It contains the following data:
supplier_id | supplier_name |
|---|---|
10000 | IBM |
10001 | HP |
10002 | Microsoft |
10003 | NVIDIA |
We have another table called Orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
order_id | supplier_id | order_date |
|---|---|---|
500125 | 10000 | 2022/05/12 |
500126 | 10001 | 2022/05/13 |
500127 | 10004 | 2022/05/14 |
Let’s create an agent with two Web Element List commands. The first one is called Suppliers that will extract data from Suppliers table, and the second one is called Orders that will extract data from Orders table.
Notice that the supplier_id column in the Suppliers table refers to the supplier_id in the Orders table. The relationship between the two tables above is the supplier_id column.
We will use a Data List command which will call Result Data List in order to join Suppliers and Orders Web Element Lists. In this example we will use Inner Join.
The Result Data List should have the following configuration:
Data provider: Data Table Join
Data List 1: Suppliers
Key 1: supplier_id
Data List 2: Orders
Key 2: supplier_id
Join Type: Inner Join
Our Result Data List would produce data that look like this:
supplier_id | supplier_name | order_id | order_date |
|---|---|---|---|
10000 | IBM | 500125 | 2022/05/12 |
10001 | HP | 500126 | 2022/05/13 |
It is similar to executing the following SQL query
SELECT Suppliers.supplier_id, Suppliers.supplier_name, Orders. order_id, Orders.order_date
FROM Suppliers
INNER JOIN Orders
ON Suppliers.supplier_id = Orders.supplier_id
It is possible to join Web Element Lists or Data Lists on more than one column (key). Just use ‘Add Another Key’ button if you wish to add another key.
Currently, it is only possible to join two Web Element Lists or Data Lists at the time. If you need to join more than two, you could do that in multiple steps. For example, join Data List 1 and Data List 2, which will produce Result Data List. Then, join Result Data List and Data List 3, and so on.