Joins
SQL supports joins within a database. For example, many companies do not store Customer Name in the order detail file. Marketing usually wants to know Sales by customer. Hence the need to JOIN these two files.
Both files usually share a common piece of data, known as the key. In this example, the key is customer number.
The join statement looks like this (the name before the period references the source table, in this case we reference both Customer table and Orders table):
WHERE Customer.CustomerNumber = Orders.CustomerNumber.
We will need to add some fields from the Orders file, such as:
SalesQ1, SalesQ2, SalesQ3, SalesQ4
Once more than one table is referenced, you should put the table identifier in front of each field. So, CompanyName really looks like Customer.CompanyName.
Putting it all together looks like:
SELECT Customer.CompanyName, Customer.CompanyAddress, Customer.CompanyCity, Orders.SalesQ1, Orders.SalesQ2, Orders.SalesQ3, Orders.SalesQ4 FROM Customers, Orders WHERE Customer.CompanyCity = "Boston" AND Customer.CustomerNumber = Orders.CustomerNumber.
The resultant table will load nicely into Notes, and look like it has always been joined!
If you want to roll up sales, you can do it. Remember that all the data will be summed according to all non aggregated fields. In this example, you would have one summary record per CompanyName, CompanyAddress and CompanyCity. If you wanted sales by CompanyName only, it does not make sense requesting Address and City. That statement looks like:
SELECT Customer.CompanyName, SUM(Orders.SalesQ1), SUM(Orders.SalesQ2), SUM(Orders.SalesQ3), SUM(Orders.SalesQ4) FROM Customer, Orders WHERE Customer.CustomerNumber = Orders.CustomerNumber GROUP BY Customer.CompanyName.
Using a Notes Formula to Create a Calculated Field
If you wanted TotalSales, you could add a Notes formula that looks like:
Field TotalSales := SalesQ1 + SalesQ2 + SalesQ3 + SalesQ4.
Important Note: Make sure all tables referenced in the Select Statement are also listed in the Table Names section of the form, or the Sentinel will not know there are more than one table to be joined. Table NamesCustomer, Orders.
Note: Not all drivers support all functions. The Sentinel passes any failure codes bask to the user, and writes them to the Message Log for future reference.