Querying Multiple Tables and Views

  • Article

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.

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 FoxPro \Samples\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.

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.

See Also

Add Table or View dialog box | Join Condition dialog box | Controlling Record Selection with Joins | Customizing Queries | Creating Queries | Creating a Query | Running Queries