Chapter 6: Querying and Updating Multiple Tables

With Visual FoxPro, you can query multiple tables and views, and combine remote and local data into a view. This capability is useful when the data you need is stored in two or more tables, or split between local tables and tables on a remote data source.

For information about setting up simple one-table queries and views, see Chapter 4, Retrieving Data, and Chapter 5, Updating Data with Views, For more information about joins, see Chapter 7, Working with Tables, in the Programmer’s Guide.

This chapter discusses:

Querying Multiple Tables and Views

When you need access to information stored in two or more tables, just add all the relevant tables to your query and join them on common fields. Then you can search the records in all the tables for the information you need. You can use any combination of database tables, free tables, and local or remote views in your queries.

Using multiple tables or views in a query

Because the term “view” can refer to both an input source or the object you are creating, the term “table” is used for both tables and views used as input sources. The term “view” is used for the object you create with the View Designer.

Adding Views and Tables to a Query

When you add tables or views to your query, Visual FoxPro suggests a possible join between the tables or views, based on matching field names.

For example, if you add the Customer table in the Visual Studio …\Samples\Vfp98\Data directory to the Query Designer, and then add the Orders table, Visual FoxPro suggests a join between the tables based on the matching fields Customer.cust_id and Orders.cust_id.

Suggested join between tables

If you are using a database that contains persistent relationships defined between the views or tables within it, Visual FoxPro uses the existing relationships as default joins.

If you want to add database tables or views to your query, you may need to open the appropriate database to make them available.

To add a table or view to a query

  1. In the Query Designer toolbar, choose Add Table.

  2. In the Add Table or View dialog box, select the database you want to use, choose Tables or Views, select the table or view you want to add, and choose OK.

    -or-

    If you want to add a table that is not part of the database, choose Other, locate the table in the Open dialog box, and choose OK.

  3. In the Join Condition dialog box, verify the suggested join.

    If Visual FoxPro cannot suggest a likely match between fields, select the matching fields in the Join Condition dialog box yourself.

  4. Choose OK.

Controlling Record Selection with Joins

With multiple tables in your query, you can control which records your query selects by altering or adding joins. Using the Join Condition dialog box, you can change the join type set between the tables.

Joins appear automatically when you add the tables. However, if the field names of related fields do not match, you might have to create your own joins between tables. You can create additional joins by dragging between the fields of tables in the Query Designer, or by choosing the Add Join button in the Query Designer toolbar to display the Join Condition dialog box.

Join Condition dialog box

When you add or alter a join, you can select a type of join to expand or narrow your results. The easiest way to create a join is by using the Join Condition dialog box.

To create a join between tables

  1. Add two or more tables to a query.

  2. In the Query Designer toolbar, choose Add Join.

  3. In the Join Condition dialog box, select the related field names in the two tables.

    Note   Join columns only if they have the same size and data type.

  4. Select a join type:
    To retrieve… Use…
    Only records from both tables that match the join criteria, the most common type of join Inner Join
    All records from the table on the left side of the join criteria and only records that match the join criteria from the table on the right side of the join criteria Left Join
    All records from the table on the right side of the join criteria and only records that match the join criteria from the table on the left side of the join criteria Right Join
    All records from both tables whether or not they match the join criteria Full Join

  5. Choose OK.

For more information about joins, see Chapter 8, Creating Views, in the Programmer’s Guide.

You can also delete or modify existing joins. Although you can still run a query without the joins, the results usually have little practical value and may require large amounts of time to process.

To delete a join

Besides filters and join types, you can control your results by changing the criteria used in the join. Joins do not have to be based on an exact match of fields; you can set up different join relationships based on Like, Exactly Like, More Than, or Less Than criteria.

Join criteria are similar to filter criteria; they both compare values and then include records that match the criteria. Unlike a filter which compares a field value to a filter value, the join criteria compares the field value from one table to the field value of the other table.

For example, when you query two tables joined on their respective customer ID fields (Customer.cust_id = Orders.cust_id), the query retrieves only the records in which these two fields match, and which also meet any other filter you set in the query.

To modify a join

  1. In the Join tab, select the join you want to modify.

  2. Change the join conditions as needed.

For more information about logical operators used in join criteria, see Logical Operators.

Using Multiple Tables in Views

You can also combine two or more local tables in a local view. The process of defining a multi-table view is similar to working with multiple tables in a query. But using views gives you the added capability to update the source tables when working with the output of the view.

For example, you can join the Customer and Orders tables, from the Visual Studio …\Samples\Vfp98\Data directory, in a view, and set criteria to select a subset of the combined records. As you update the results in a Browse window or in a form, the updates you make can be sent back to update the source tables.

Combining tables in a view

To join tables in a view

  1. Add a table in the View Designer.

  2. Choose Add Join, change the join conditions if you wish, and choose OK.

To enable updating of the source tables

  1. In the View Designer, select the Update Criteria tab.

  2. Select Send SQL updates.

    Note   To activate the Send SQL updates option you must first set a key field in one of the tables.

For more information about controlling updates to tables, see Chapter 17, Programming for Shared Access, in the Programmer’s Guide.

Using Multiple Remote Tables in a View

When you connect to a remote data source, you can gain access to many related tables. You can select the tables you need and adjust the relationships between them, if necessary, to get the information you need.

For example, suppose you are connected to a publications database, which contains many interrelated tables. If you are searching for information about titles and authors, you could use three related tables: Authors, Titles, and Titleauthor.

Multiple tables in a remote view

To create a multitable remote view

  1. From the File menu, choose New, select Remote View, and choose New File.

  2. In the Select Connection or Data Source dialog box, select a predefined connection or an available data source.

  3. If required, log on to the server.

  4. In the Open dialog box, select the tables you want to use.

  5. In the Join Condition dialog box, accept the default join, or set up a join as needed.

As with local views, you can use the options in the Update Criteria tab in the View Designer to control how updates are made to the source tables.

Combining Local and Remote Data in a View

If data you need is stored on a remote data source, and is related to information in a local table, you can combine the local and remote data in a view.

First, you can create a remote view to select only the relevant records you want to work with from the remote server; then, using a local view, you can join the remote view with the related local table.

For example, suppose you have a large master customer database on a server and a local table that stores country code information. You can create a remote view, connect to the server, and select customer records of interest; then create a local view and add the remote view and the local table to it.

As you work with the records in the local view to enter new orders, you can choose to update the remote view and the local table. If you close the local view and then close the remote view, the remote view will update the table on the remote customer database.

Remote view and local table in a local view

To combine remote and local data in a view

  1. Create a remote view and add one or more tables from the remote server.

  2. Create a new local view and add the remote view you just created to it.

  3. Add the related local table to the view and join them on a common field.

  4. Set a filter in the local view and run it.

  5. Update the view results to update both the local table and the remote view.

  6. Close the local view and then the remote view to update the data on the remote server.

For more information about working with views within other views, see “Combining Views” in Chapter 8, Creating Views, in the Programmer’s Guide.