Objective
To see the effect of automatic joins in the SQL Wizard.
Background Information
In SQL, there is a term known as JOIN. This means that two tables can be operated on at the same time in such a scenario where one table has a Primary Key, and the other has a Foreign Key which matches the primary key of the first table. Essentially, this means all of the data in the row of Table 2 which has the foreign key is related to the data in the row in Table 1 with the matching primary key.
For the purposes of working with templates in AutoTag, it is important that you at least understand the requirements to join two tables: One table has a primary key (For example, the Employees table has an EmployeeID column) and one table has a matching foreign key (For example, the Orders table also has an EmployeeID column).
In the context of more advanced SQL knowledge, this primary-foreign key relationship helps us store data with a one-to-many relationship (So one employee could have many orders).
Pre-requisites
Inserting a Tag, ForEach Tag, SQL - Part 1: Basic SelectionStarting Template
Start with the SQL Wizard open. It doesn’t matter what ForEach tag you are on, or what template you have open. If you don’t know how to open this wizard yet, create a ForEach tag with a SQL datasource, and click the Wizard button in the AutoTag ribbon, or check outSQL - Part 1: Basic Selection.
Procedure
1. Drag FirstName and LastName from Employees
First we’ll drag in the FirstName and LastName columns from the Employees table. This is pretty self-explanatory by now.
2. Drag CompanyName and ContactName from Customers
Now, we drag CompanyName and ContactName from Customers. If you read the Background Information section above, you know that one requirement for SQL joins is that one table has a primary key, and the other has a foreign key. Well looking through the Customers table, there is no key except CustomerID. In the next step we’ll find out just what happened with the automatic joins (which actually used a middle-man table, Orders to associate Customers with every Employee they have ordered from)
3. View the automatically detected joins
As you can see, Orders has an EmployeeID and CustomerID foreign keys, so we can join Employees to Orders—employee A took care of order 1—and we can join Customers to Orders—customer C was on the receiving end of order 1. Thus, order 1 relates employee A to customer C.
