Setting Up Foxfire! v6.0 for Building Multi-Table OUTER JOINs
One of the main new features in Foxfire! v6.0 is it’s ability to generate SQL using the SQL-92 join operators that were added to Visual FoxPro’s SQL implementation way back with the release of Visual FoxPro 5.0. The primary benefit of this new capability is the ability to build multi-table OUTER JOINs. This article will discuss building OUTER JOINs using Foxfire! v6.0 and how to properly setup your database’s relational structure in Foxfire! to support building queries using the SQL-92 join syntax.
The SQL generation process in Foxfire! v6.0 revolves around a new component of the Foxfire! metadata call the “Join Tree”. The Join Tree is the device that you use to define the table relationships and hierarchy of those relationships in your database that the Foxfire! SQL generator then uses to build SQL that is compliant with the SQL-92 join syntax, so a good portion of this article will discuss how to setup the Join Tree. But first, I will briefly review the SQL-92 join syntax and OUTER JOINs, and the limitations of FoxPro’s earlier SQL implementation that necessitated addition of the SQL-92 join operators.
An OUTER JOIN is a join that preserves all the rows in one or both tables, even when they do not have matching rows in the second table. Let’s take a look at an example. The Foxfire! sample database that is installed with the product models the business of a car wholesaler. Cars are purchased, repaired if necessary, and then resold. There is a Cars table and an Expenses table for tracking repairs to Cars. The two tables are related by a STOCKNO field. A typical report from this database might want to show all Cars that haven’t been sold and a list repairs for each Car. A query to produce this report using FoxPro’s earlier SQL implementation required the use of a WHERE clause (or a “natural join”) to relate the two tables and filter the results by empty Cars Sale Date.
SELECT ALL;
CARS.MAKE,;
CARS.MODEL,;
EXPENSES.EXPTIME,;
EXPENSES.RODATE,;
EXPENSES.ROTOTAL
FROM "CARS" CARS,;
"EXPENSES" EXPENSES;
WHERE CARS.STOCKNO = EXPENSES.STOCKNO;
AND Cars.Saledt={}
A limitation of the above query is that the report will only show those Cars that incurred Expenses. This happens because the natural join will only return records where the Cars and Expenses have matching STOCKNO values. What if you want to show all Cars whether or not they have incurred Expenses? Returning all Cars whether or not they have Expenses requires an OUTER JOIN. More specifically, a LEFT OUTER JOIN, since there is a one-to-many relationship between Cars and Expenses, with the Cars table being on the “left”, “parent”, or “one-side” of the relationship, and the Expenses table being of the “right”, “child”, or “many-side” of the relationship.
Below is a rewrite of the query using a LEFT OUTER JOIN to return all Cars whether or not there are matching Expenses. The WHERE clause is still used to filter the results by empty Cars Sale Date.
SELECT ALL;
CARS.MAKE,;
CARS.MODEL,;
EXPENSES.EXPTIME,;
EXPENSES.RODATE,;
EXPENSES.ROTOTAL
FROM "CARS" CARS;
LEFT OUTER JOIN "EXPENSES" EXPENSES ;
ON Cars.Stockno = Expenses.Stockno;
WHERE Cars.Saledt={}
The SQL-92 syntax used above places the table JOIN as an operator in the FROM clause and allows you to apply a specific join type directly within the JOIN expression. In addition to the LEFT OUTER JOIN, there are three other join types supported by Visual FoxPro’s SQL implementation. Here’s a summary of all four.
INNER JOIN – return only matching records
LEFT OUTER JOIN – return all “parent” records whether or not there are matching “child” records
RIGHT OUTER JOIN – return all “child” records whether or not there are matching “parent” records
FULL OUTER JOIN – return all matching and non-matching records from both tables
Building OUTER JOINS with Foxfire! v6.0
Foxfire! dynamically builds SQL based on user selected report criteria. If users select data items from more than one table, Foxfire! needs to know how those tables are related. When building the SQL to relate two or more tables, the Foxfire! SQL generator refers to a Relationships table to find out how to join all the tables in the query. Therefore, the key to Foxfire! generating the correct SQL for a report has always been the proper setup of the Relationships table.
Each record in the Relationships table represents a join between two tables in the database. To add, edit, or delete Relationship records you use the Relationship Editor utility. Figure 1 below shows the Foxfire! Relationship Editor with all the relationships for the Foxfire! sample database loaded.
Figure 1. “The new Foxfire! Relationship Editor”
The Relationships Editor has changed quite a bit in Foxfire! v6.0. A Join Tree has been added to allow you to graphically specify how tables are related in your database. More specifically, the Join Tree lets you define the hierarchical relationship for each join and how joins are related to other joins, thus forming a tree structure representing all joins in the database. A “Join Type” field has also been added to the Relationships table. Using the Relationships Editor you can now specify an INNER, LEFT OUTER, RIGHT OUTER, or FULL JOIN for each join in the Join Tree.
The addition of the Join Tree to the Relationships Editor was necessary because it is critical when building OUTER JOINS to know which table is the Parent table, which table is the Child table, and the hierarchical sequence of all the joins. The Foxfire! Join Tree lets you specify exactly how the joins should be sequenced when Foxfire! dynamically builds the SQL. A simple tree parsing algorithm is used when building the SQL so that the generated JOIN clauses will map exactly to the structure of the Join Tree.
For example, referring again to the Foxfire! sample database and the example Cars-Expenses query; what if a user picks another data item (EXP_DESC), and that data item is from the ExpTypes table, which is related to the Expenses table as a child table, thus adding another join to the query. The sample query will be built as follows:
SELECT ALL;
CARS.MAKE,;
CARS.MODEL,;
EXPENSES.EXPTIME,;
EXPENSES.RODATE,;
EXPENSES.ROTOTAL,;
EXPTYPES.EXP_DESC
FROM "CARS" CARS;
LEFT OUTER JOIN "EXPENSES" EXPENSES ;
INNER JOIN "EXPTYPES" EXPTYPES ;
ON Expenses.Exptype = Exptypes.Exp_code ;
ON Cars.Stockno = Expenses.Stockno
In the above query, you can see that a second join has been added, an INNER JOIN, that relates the Expenses table to the ExpTypes table. The joins are built in a sequence and nested structure that exactly maps to structure of the Join Tree.
The proper sequence of the joins in the SQL statement is important because of how the joins will be executed by the database Sql engine. The joins are not executed simultaneously as you might expect. They are executed one after the other with the each subsequent join being applied against the intermediate results of previously executed joins.
Errors will result if you don’t put the joins in the right sequence. In the SQL below, the sequence of the joins has been switched such that the Expenses table is listed in the FROM clause, followed by the INNER JOIN with the ExpTypes table, followed by a INNER JOIN with the Cars table. An error will occur declaring that the field “STOCKNO” cannot be found. This happens because the Visual FoxPro SQL engine cannot find the Expenses.Stockno reference because the Expenses table has not yet been opened. When using the “nested” join structure, the joins are processed from inside-out. The JOIN keyword has the effect of opening the table that immediately follows it, just as the FROM keyword opens the table immediately following, and therefore the first two tables opened are the Cars and ExpTypes tables. To get the query to work, the Exptypes table would need to be put in the FROM clause.
SELECT ALL;
CARS.MAKE,;
CARS.MODEL,;
EXPENSES.EXPTIME,;
EXPENSES.RODATE,;
EXPENSES.ROTOTAL,;
EXPTYPES.EXP_DESC
FROM "EXPENSES" EXPENSES;
INNER JOIN "EXPTYPES" EXPTYPES ;
INNER JOIN "CARS" CARS ;
ON Cars.Stockno = Expenses.Stockno ;
ON Exptypes.Exp_code = Expenses.Exptype
The purpose of the Join Tree is to let you predefine how all the tables are related so that you and your users do not run into these types of SQL errors.
What’s the right join tree structure?
From the above discussion you may be wondering how do you setup the right Join Tree structure for your own database. What is the correct join sequence? To answer that question, you simply have to look at how you would like the joins built for your queries and then map that structure into the Foxfire! Join Tree. Most databases have relational patterns that readily lend themselves to a hierarchical structure, and therefore map easily into the Join Tree.
For example, the classic Customer, Orders, and Order Items database follows a straightforward Parent – Child – Grand Child join sequence. The following example SQL was generated using Foxfire! for the Northwinds sample database that ships with ACCESS.
SELECT ALL
Customers.[CompanyName] AS CompanyName,
Orders.[OrderDate] AS OrderDate,
Order_Details.[Quantity] AS Quantity,
Order_Details.[UnitPrice] AS UnitPrice
FROM ([Customers] Customers
LEFT JOIN ([Orders] Orders
LEFT JOIN [Order Details] Order_Details
ON Orders.[OrderID] = Order_Details.[OrderID])
ON Customers.CustomerID = Orders.CustomerID)
The Join Tree for this database is set up as shown in Figure 2 below.
Figure 2. “A sample Join Tree for a standard Customers database”
Setting up the join tree for star schemas
A far less intuitive Join Tree structure, yet very common structure, results from databases that have tables related in the classic star schema. Star schemas result from the use of link tables to form many-to-many relationships among two or more tables.
Let’s look at another example database to demonstrate how to handle star schemas. This time I’ll setup the Join Tree for the Books sample dbc that ships with VFP 6.0. Figure 3 below shows the Books DBC opened in the VFP Database Designer.
Figure 3. “The Books DBC as diagramed in the Visual FoxPro Database Designer”
In the above figure you can see that the BookAuth table is the link table between the Authors table and the Books table. Both Authors and Books have a one-to-many relationship with the BookAuth table. Additionally, there is one-to-many relationship between Topics and Books. Put in plain English, Authors can write more than one Book, Books can have more than one Author, and many Books can fall under the same Topic.
The common mistake when building a Join Tree when link tables are involved is to put all the “one-side” tables as “parent” nodes in the Join Tree. This would result in the following tree as shown in Figure 4.
Figure 4. “This Join Tree is not built correctly for the Books database”
Before trying to build the Join Tree, let’s look at how we would write a SQL query involving all four tables in the database. What if we wanted a report on all Authors, regardless of whether or not those Authors had written a book, then list of the Books each wrote with the Topic associated with each Book listed. We would need the following SQL:
SELECT ALL;
AUTHORS.FIRST_NAME,;
BOOKS.TITLE,;
TOPICS.TOPIC
FROM "BOOKS!AUTHORS" AUTHORS;
LEFT OUTER JOIN "BOOKS!BOOKAUTH" BOOKAUTH ;
INNER JOIN "BOOKS!BOOKS" BOOKS ;
INNER JOIN "BOOKS!TOPICS" TOPICS ;
ON Books.Topic_id = Topics.Topic_id ;
ON BookAuth.Book_id = Books.Book_id ;
ON Authors.Author_id = BookAuth.Author_id
Looking at the above SQL, you can see that the Books table is hierarchically below the BookAuth link table, and the Topics table is hierarchically below the Books table. This may seem counter intuitive given the one-to-many relationships in the schema, but remember, the joins are not executed simultaneously, and therefore you need to decide a sequence for the joins. Authors and Books could be switched, with Books being put at the top of the hierarchy, but with Topics also in the query, I chose Authors to be at the top because of the relationship between Books and Topics.
If we map the above join structure into the Foxfire! Join Tree, we get the following tree as shown in Figure 5.
Figure 5. “This is the correct Join Tree for the Books database”
The rule of thumb when you have link tables is that you need to choose one table to be on the Parent side of the link table, and all other tables related to the link table on the Child side of the link table, regardless of how many tables there are. Another important general rule to follow is to try to keep all related tables within a single Join Tree “branch”.
Circular Joins
Database schemas that contain circular table references have been problematic for Foxfire! in previous versions. If there was more than two relational paths between two tables, and you created a report that required those two tables to be joined, Foxfire! would often generate incorrect SQL because of the ambiguity as to which relational path to use to relate the tables. The Join Tree structure and use of SQL-92 join operators helps resolve many of these problems.
The Students and Classes sample database is an example of a database with circular joins. Figure 6 below shows the database diagramed in the VFP Database Designer.
Figure
6. “The sample Students and Classes DBC
as diagramed in the Visual FoxPro Database Designer”
In the above Figure, you can clearly see a circular relationship between Classes and Students. Using the Join Tree, you can break the circle and thus make it easier for Foxfire! Sql generator to pick the best way to build the join when the Students table is referenced. Figure 7 below shows the Join Tree for the Stundents and Classes database.
Note: this is also a good example of building a Join Tree for databases that have link tables. There are in fact three link tables. Pay particular attention to how the Instructors and Departments link was handled.
Figure 7. “The Join Tree for the Students and Classes sample database”
Using the above Join Tree, if a user wants to see a list of all Students, the Students Classes, the Classes Instructor, and the Assignments for the class, the Foxfire! Sql generator will build the following SQL:
SELECT ALL;
STUDENTS.LASTNAME,;
CLASSES.CLASSNAME,;
ASSIGNMENTS.ASSIGNMENTDESCRIPTION,;
INSTRUCTORS.INSTRUCTOR
FROM "STUDENTS AND CLASSES!INSTRUCTORS" INSTRUCTORS;
INNER JOIN "STUDENTS AND CLASSES!CLASSES" CLASSES ;
INNER JOIN "STUDENTS AND CLASSES!ASSIGNMENTS" ASSIGNMENTS ;
ON Classes.Classid = Assignments.Classid ;
INNER JOIN "STUDENTS AND CLASSES!STUDENTS_AND_CLASSES" STUDENTS_AND_CLASSES ;
RIGHT OUTER JOIN "STUDENTS AND CLASSES!STUDENTS" STUDENTS ;
ON STUDENTS_AND_CLASSES.STUDENTID = STUDENTS.STUDENTID ;
ON Classes.Classid = Students_and_classes.Classid ;
ON Instructors.Instructorid = Classes.Instructorid
Notice the RIGHT OUTER JOIN on Students in this query. To get all Students regardless of whether or not they have signed up for any Classes, a RIGHT OUTER JOIN was used because the Students table is on the “right” side of the link table (Students_and_Classes) relationship between Classes and Students. You can make this RIGHT OUTER JOIN the default for the Students_and_Classes to Students relationship, or change the join type when creating a report.
In summary, the Join Tree and Join Type metadata continues the main theme in Foxfire!’s design, and that is to allow the developer to impart his or her knowledge of the database into a metadata layer to hide the SQL complexity from the user. Users can build queries and reports that require OUTER JOINs without having to learn the complexities of the SQL-92 join standard.
Next time I will dig deeper into Foxfire!’s relationship metadata to demonstrate other advanced options for setting up Foxfire! to build other types of advanced queries.