This Zip file contains sample programs and data related to "Building Queries in Visual FoxPro" by Tamar E. Granor. All examples are copyright 2002 by Tamar E. Granor, Ph.D. They may be used for the purpose of learning and teaching Visual FoxPro. 

The examples here are designed for demonstration of various features of Visual FoxPro. They are intended to be neither bullet-proof code, nor a demonstration of proper coding practices.

Some of the examples here use data from the TasTrade sample database, which comes with Visual FoxPro. It may be necessary to point to the data before running some of the examples. In VFP 6 and later, the TasTrade database is found in the directory _SAMPLES + "TasTrade\Data"

Some examples use the Membership database which is included in this zip file. The database maintains membership information for an organization like a YMCA. The following tables are used in the examples:

Family - one record per family.
Person - one record per person. Points to a family record and to a person record for an emergency contact.
Phone - one record per phone number.
HasPhone - many-to-many link between Person and Phone. One record for each phone for each person.

The database also contains an Ids table used for giving each record a primary key.

The following programs are contained in this Zip file. Programs are listed in approximately the order they are used in the session notes and are grouped based on the material they cover. Programs with names ending "bad" fail or produce erroneous results. For some examples, both a PRG and a QPR is provided. In those cases, the QPR can be opened in the VFP 8 Query Designer, but may not work in earlier versions. 

NestedJoin - Demonstrates the nested style for multiple joins.
SeqJoin - Demonstrates the sequential style for multiple joins.

NestBad - Nested join syntax can produce bad results, especially when dealing with "multiple unrelated siblings."
NestOdd - Multiple unrelated siblings can be handled with nested syntax, but the query is hard to read.
NestOrderLast - Putting the parent table last in the nested syntax works for multiple unrelated siblings, but the query is hard to read.
Sequent - Sequential join syntax is easier to read than nested when dealing with multiple unrelated siblings.

Mixed - Nested and sequential join syntax can be combined. This query collects order information including the name of the employee taking the order.

Contacts - Use a self-join to produce a list of all people and their emergency contacts. 
ContactPhone - Use a self-join to produce a list of all people, their emergency contacts and the contacts' phone numbers.

Recent - Use an outer join to create a list of all customers and the date of their most recent order.

AllPhonesBad - With inner joins only, you can't produce a list of all people and their phone numbers.
AllPhones - Use outer joins to produce a list of all people and phone numbers.
AllPhonesNested - Use outer joins with the nested syntax to produce a list of all people and phone numbers.

HomePhonesBad - Filtering with WHERE based on a field in the child table in an outer join omits some records.
HomePhones - Put a filter condition in the join clause to provide a list of all people and their home phones, if available.
KidHomePhones - Some filters in an outer join can go in the WHERE clause as when making a list of all children and their home phones.
ComplexFilter - Use parentheses to handle a complex filter/join condition. 

CountOrdersBad - Using COUNT(*) with an outer join produces unexpected results.
CountOrders - Include a field name with COUNT() to ensure that records added by an outer join are not counted.

NoHome - Use a subquery to find a list of people with no home phones listed.

OldestSub - Use a subquery to find the oldest member of each family.
OldestSeq - Find the oldest member of each family. This version uses two queries in sequence rather than a subquery.

AllComps - Use UNION to list all companies in TasTrade.
AllCompsAddr - Use UNION and dummy fields to list all companies in TasTrade and their addresses.

DummyMemo - Show how to add an empty memo field to make field lists match up in a UNIONed query.

CustProf - Use UNION and some tricks to produce the data for a "customer profile" report that includes a list of employees who've taken orders for the customer and products the customer has orderd.


The Zip file contains one report:

CustProf.FR? - This report shows the "customer profile" (described for CustProf.PRG above). Run CustProf.PRG before using the report.

The Zip file contains the following forms:

Optimization.SCX/SCT - Demonstrates the SYS(3054) function for testing optimization, and the various optimization effects.

Vary3050.SCX/SCT - Demonstrates the effect of the SYS(3050,1) setting on query speed.

WhereVsHaving.SCX/SCT - Demonstrates the optimization difference of putting a filter in the Where clause rather than the Having clause.

The Zip file contains the following class libraries, used to support the forms:

_Base.VCX/VCT
Buttons.VCX/VCT
Controls.VCX/VCT
Forms.VCX/VCT
NewLang.VCX/VCT