In the previous article found here: Sage 100 2013 ODBC Help I went over the basic function of using SQL commands with a Sage 100 system. In this article I will go over the advanced feature including joins. The first join I will show is what Sage calls the Cross Join:

SELECT TOP 200 * from SO_SalesOrderHeader, SO_SalesOrderDetail

The cross join returns all records from table B for each common record in table A. The * is going to return all rows from table A and table B. I am not sure when a cross join would be useful comments are welcome to let me know. When limiting the columns returned it’s a good idea to name the tables using an alias. Such as the following example using the aliases A and B:

SELECT TOP 200 A.SalesOrderNo, B.ItemCode FROM SO_SalesOrderHeader A, SO_SalesOrderDetail B

This example above is really not that useful. To make a join that would be better suited for a program or report you would want to use an INNER JOIN or an OUTER JOIN.

An inner join is where you join two tables on a common column matching on a specific field, such as a common key, where matching rows are returned. The inner join will discard unmatched rows in both tables. It’s this example the ProvideX ODBC driver diverges from “common” SQL syntax. In this example I need to add the join syntax between braces { }
SELECT TOP 200 A.SalesOrderNo, B.ItemCode FROM { oj SO_SalesOrderHeader A INNER JOIN SO_SalesOrderDetail B ON A.SalesOrderNo = B.SalesOrderNo }

According to the ProvideX documentation for ODBC, linked in the previous article, the word INNER can be ignored. So what’s going here is after the first brace the oj denotes outer join and then the table name which will be the outer join table. In this case my I will outer join the SO_SalesOrderHeader table with the alias A. The inner join table is noted with the wording INNER JOIN and in my case the outer join table is SO_SalesOrderDetail with the alias B. The ON section lets the system know which columns to find the matching record. It might seem bit confusing to have the outer join syntax but if you consider that if your goal is to have two database tables joined on matching rows consider a Venn diagram were you have two circles that overlap. An inner join is where the two circles intersect on the Venn diagram, and in such a case there are at least two outer sections. In my case with the ProvideX syntax the oj section just lets the driver know which table will contain the outer joined data, and the working inner join let’s the driver know that the outer data will be discarded.

The next section will demonstrate a Left Outer Join or just left join. A left join will include all data from table A and only the matching data from table B. An example of using this kind of join would be if you wanted to see all the sales orders where there is a matching invoices. Just to show the syntax I will use the same statement as above substituting the inner join syntax for left outer join.
SELECT TOP 200 A.SalesOrderNo, B.ItemCode FROM { oj SO_SalesOrderHeader A LEFT OUTER JOIN SO_SalesOrderDetail B ON A.SalesOrderNo = B.SalesOrderNo }

Just like the inner join the ProvideX documentation states that the word OUTER can be removed. Also in my examples I have use the TOP 200 to only show 200 records speeding up the time it takes to ruin the query as it will only return at most 200 rows.