Uses the tables provided as input to feed the parameter in the built statement. The
statement can include inner or outer joins to be implemented between tables or between one
table and its aliases.

The three ELT Mysql components are closely related, in terms of their
operating conditions. These components should be used to handle Mysql DB schemas to
generate Insert statements, including clauses, which are to be executed in the DB output
table defined.

tELTMysqlMap helps to graphically build the SQL
statement using the table provided as input.

This scenario describes a Job that gathers together several input DB table schemas and
implementing a clause to filter the output using an SQL statement.

• Drop the following components from the Palette
  onto the design workspace: three tELTMysqlInput
  components, a tELTMysqlMap, and a tELTMysqlOutput. Label these components to best describe
  their functionality.

• Double-click the first tELTMysqlInput component
  to display its Basic settings view.

• Select Repository from the Schema list, click the three dot button preceding Edit schema, and select your DB connection and the
  desired schema from the [Repository Content] dialog
  box.

  The selected schema name appears in the Default Table
  Name field automatically.

  In this use case, the DB connection is
  Talend_MySQL and the schema for the
  first input component is owners.

• Set the second and third tELTMysqlInput
  components in the same way but select cars and
  resellers respectively as their schema names.

• Connect the three tELTMysqlInput components to
  the tELTMysqlMap component using links named
  following strictly the actual DB table names: owners,
  cars and resellers.

• Connect the tELTMysqlMap component to the
  tELTMysqlOutput component and name the link
  agg_result, which is the name of the database table you
  will save the aggregation result to.

• Click the tELTMysqlMap component to display its
  Basic settings view.

• Select Repository from the Property Type list, and select the same DB connection that you use
  for the input components.

  All the database details are automatically retrieved.

• Leave all the other settings as they are.

• Double-click the tELTMysqlMap component to launch
  the ELT Map editor to set up joins between the input tables and define the output
  flow.

• Add the input tables by clicking the green plus button at the upper left corner of
  the ELT Map editor and selecting the relevant table names in the [Add a new alias] dialog box.

• Drop the ID_Owner column from the owners
  table to the corresponding column of the cars table.

• In the cars table, select the Explicit
  join check box in front of the ID_Owner column.

  As the default join type, INNER JOIN is
  displayed on the Join list.

• Drop the ID_Reseller column from the
  cars table to the corresponding column of the
  resellers table to set up the second join, and define the
  join as an inner join in the same way.

• Select the columns to be aggregated into the output table,
  agg_result.

• Drop the ID_Owner, Name, and
  ID_Insurance columns from the owners
  table to the output table.

• Drop the Registration, Make, and
  Color columns from the cars table to
  the output table.

• Drop the Name_Reseller and City columns
  from the resellers table to the output table.

• With the relevant columns selected, the mappings are displayed in yellow and the
  joins are displayed in dark violet.

• Set up a filter in the output table. Click the Add filter
  row button on top of the output table to display the Additional clauses expression field, drop the
  City column from the resellers table
  to the expression field, and complete a WHERE clause that reads resellers.City
='Augusta'.

• Click the Generated SQL Select query tab to
  display the corresponding SQL statement.

• Click OK to save the ELT Map settings.

• Double-click the tELTMysqlOutput component to
  display its Basic settings view.

• Select an action from the Action on data list as
  needed.

• Select Repository as the schema type, and define
  the output schema in the same way as you defined the input schemas. In this use
  case, select agg_result as the output schema, which is the name
  of the database table used to store the mapping result.

• Leave all the other settings as they are.

• Save your Job and press F6 to launch it.

  All selected data is inserted in the agg_result table as
  specified in the SQL statement.

This scenario describes a Job that maps information from two input tables and an alias
table, serving as a virtual input table, to an output table. The
employees table contains employees’ IDs, their department numbers,
their names, and the IDs of their respective managers. The managers are also considered as
employees and hence included in the employees table. The
dept table contains the department information. The alias table
retrieves the names of the managers from the employees table.

• Drop two tELTMysqlInput components, a tELTMysqlMap component, and a tELTMysqlOutput component to the design workspace, and label them to
  best describe their functionality.

• Double-click the first tELTMysqlInput component
  to display its Basic settings view.

• Select Repository from the Schema list, and define the DB connection and schema by clicking the
  three dot button preceding Edit schema.

  The DB connection is
  Talend_MySQL and the schema for the first input
  component is employees.

• Set the second tELTMysqlInput component in the
  same way but select dept as its schema.

• Double-click the tELTMysqlOutput component to
  display its Basic settings view.

• Select an action from the Action on data list as
  needed, Insert in this use case.

• Select Repository as the schema type, and define
  the output schema in the same way as you defined the input schemas. In this use
  case, select result as the output schema, which is the name of
  the database table used to store the mapping result.

  The output schema contains all the columns of the input schemas plus a
  ManagerName column.

• Leave all the other parameters as they are.

• Connect the two tELTMysqlInput components to the
  tELTMysqlMap component using Link connections named strictly after the actual input
  table names, employees and dept in this
  use case.

• Connect the tELTMysqlMap component to the
  tELTMysqlOutput component using a Link connection. When prompted, click Yes to allow the ELT Mapper to retrieve the output table
  structure from the output schema.

• Click the tELTMysqlMap component and select the
  Component tab to display its Basic settings view.

• Select Repository from the Property Type list, and select the same DB connection that you use
  for the input components.

  All the DB connection details are automatically retrieved.

• Leave all the other parameters as they are.

• Click the three-dot button next to ELT Mysql Map
  Editor or double-click the tELTMysqlMap component on the design workspace to launch the ELT Map
  editor.

  With the tELTMysqlMap component connected to the
  output component, the output table is displayed in the output area.

• Add the input tables, employees and
  dept, in the input area by clicking the green plus button and
  selecting the relevant table names in the [Add a new
  alias] dialog box.

• Create an alias table based on the employees table by
  selecting employees from the Select the
  table to use list and typing in Managers in the
  Type in a valid alias field in the [Add a new alias] dialog box.

• Drop the DeptNo column from the
  employees table to the dept
  table.

• Select the Explicit join check box in front of
  the DeptNo column of the dept table to set
  up an inner join.

• Drop the ManagerID column from the
  employees table to the ID column of
  the Managers table.

• Select the Explicit join check box in front of
  the ID column of the Managers table and
  select LEFT OUTER JOIN from the Join list to allow the output rows to contain Null
  values.

• Drop all the columns from the employees table to the
  corresponding columns of the output table.

• Drop the DeptName and Location columns
  from the dept table to the corresponding columns of the output
  table.

• Drop the Name column from the Managers
  table to the ManagerName column of the output table.

• Click on the Generated SQL Select query tab to
  display the SQL query statement to be executed.

• Save your Job and press F6 to run it.

  The output database table result contains all the
  information about the employees, including the names of their respective
  managers.

• Scenario: Mapping data using a subquery, a related scenario using subquery

• Aggregating Snowflake data using context variables as table and connection names