Use INNER JOIN clause to match values from Elipse E3 tables.
With Elipse E3 databases, you can match the values from two or more tables into a single result. To combine these values, use SQL’s INNER JOIN clause; with this clause, you can aggregate the records of associated tables into a single query. For example, see how data from two tables are merged with this clause in the figure below.
Figure 1. How INNER JOIN clause works
INNER JOIN syntax
To add this clause to your application, use the syntax below to edit the SQL code directly:
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Example:
This example uses two tables, called Tab_Cor and Tab_Valor. When you merge them via Name field, they will return the following columns: Data(E3TimeStamp), Cor (Color), Valor (Value), Quantidade (Quantity) and ID. As a result, these columns will all be at the same query.
Then, enable SQL direct edition on the query’s SQL tab by inserting the following code:
SELECT Tab_Valor.E3TimeStamp AS Data,Tab_Cor.Nome,Tab_Cor.Cor,Tab_Valor.Valor,Tab_Valor.Quantidade,Tab_Cor.ID FROM Tab_Valor INNER JOIN Tab_Cor ON Tab_Cor.Nome = Tab_Valor.Nome ORDER BY Tab_Valor.E3TimeStamp ASC
Finally, the result retrieved from merging values from both tables will look like the one in the figure below:
Figure 2. Query result
In addition to this example, there is an attached demo application developed with Elipse E3 v4.8.352 below.
Attachments:
