Using the SQL Wizard to Join Data from Two Database Tables

The SQL Wizard in GoAnywhere MFT allows users to quickly build SQL SELECT statements for retrieving data from a database without having knowledge of the SQL syntax. This intuitive interface allows a user to choose a database server, select a schema (library), tables, columns, order by, where and join criteria for the SQL statement.

The resulting SELECT statement can be tested and embedded within a Project for future execution.

Using the Project Designer's SQL Wizard

Text

To access the SQL Wizard for building a SELECT statement, click the browse icon on the right side of the SQL Statement field.

Image
SQL Wizard
SQL Query
Text

Based on the Database Resource you are working with, the Schemas tab will populate the schemas or libraries (that contain the tables) you want to work with. To select schemas, click the checkboxes next to the schemas you want and click the right arrow button. The selected schemas will move to the right side of the page.

Click the tab labeled Tables. Use the arrow icons to move the desired tables from the Available Tables section to the Selected Tables section. This example uses the ‘customers’ and ‘orders’ tables.

Image
SQL Wizard - Table
SQL Wizard - Tables
Text

Click the tab labeled Columns. A list of all columns (fields) in the selected table(s) will be shown on the left side of the page. From this page, you can choose the columns (fields) for the SELECT statement. In this example, all available columns from both tables are selected.

Image
SQL Wizard - Columns
SQL Wizard - Columns
Text

When working with two tables that share some column names, select the option “Qualify Field Names”. Looking at the Query statement, it is now clear which tables the columns are being selected from.

Image
SQL Wizard -Qualify Field Names
SQL Wizard - Qualify Field Names

 

Text

Click the tab labeled Join. The Join page allows you to specify the criteria on how the tables should be joined together. In this example, we will join the queried data based on Customer ID. By selecting INNER JOIN, the results will contain any rows (records) from the two joined tables with matching ‘custid’ values. All other rows will be excluded from the results.

Image
SQL Wizard - Join
SQL Wizard - Join
Text

When you are done specifying the criteria for the SELECT statement, you can test the SELECT statement by clicking the Test button. This will run the statement against the database and will show the results of the SELECT statement (displaying the first few selected rows). This feature will help you verify that the SELECT statement is generating the results that you expected.

Image
SQL Wizard - Test
SQL Wizard - Test
Text

When finished, click the Select button to return to the Project Designer.

The following image represents the generated SQL Statement.

Image
SQL Statement
SQL Statement
Aug
30
Tuesday
Aug 30, 2022
9:00 - 10:00am