How to: Control Record Selection with Joins
When you have multiple tables in a query or view, you can control which records the query or view retrieves by adding or altering join conditions between the tables. Working with joins is similar for both queries and views; therefore, the procedures use queries as examples. Although you can run a query or view without joins, the results usually have little practical value and might require large amounts of time to process.
For more information about join conditions, see Join Conditions for Tables, Queries, and Views.
Adding Join Conditions
Joins usually appear automatically when you add tables to the query or view. However, if the field names of the related fields do not match, you might need to specify the join you want to have for the table you add using the Join Condition dialog box in the Query and View designers.
The process for adding a join is similar between the Query and View designers. The following procedure describes adding a join in the Query Designer.
To create a join between tables using the Query Designer
Create or open a query.
Add a table to the query so that you have two or more tables.
On the Query Designer toolbar, click Add Join.
In the Join Condition dialog box, select the related field names in the two tables.
Note
Set joins for columns only if they have the same size and data type.
Select a join type and click OK.
You can also add joins for tables that already appear on the Query or View designer surface by clicking and dragging the mouse between the fields in the tables or choosing the Add Join button on the Query or View designer toolbar to display the Join Condition dialog box. You can also create a join type using the using the FROM clause in the SQL SELECT statement.
For more information about the Join Condition dialog box, see Join Condition Dialog Box. For more information about SQL SELECT statements generated by the Query and View designers, see How to: Create Queries (Visual FoxPro), How to: View and Edit SQL Statements for Views, and SELECT - SQL Command.
For example, suppose you wanted to retrieve order information, including information about the customer who placed the order. The following example creates a view using two tables, Customers and Orders. Both the Customer and Orders tables have a customer ID field. The tables are related based on an inner join, which specifies that the results include only those rows from the Customer table that match one or more records from the Orders table.
The following code opens the sample database called TestData.dbc, creates a view using the CREATE SQL VIEW command as described in the example, and uses the FROM clause to specify the join condition as described:
OPEN DATABASE testdata
CREATE SQL VIEW cust_orders_view AS ;
SELECT * FROM testdata!customer ;
INNER JOIN testdata!orders ;
ON customer.cust_id = orders.cust_id
Modifying Join Conditions
You can modify existing joins in the following ways:
Choose a different join type to modify the scope of the records returned.
Select different fields in the table for the join.
Change the comparison operator.
Change the order of joins
The process for modifying a join is similar for both the Query and View designers. The following procedure describes modifying a join in the Query Designer.
To modify a join
Open a query containing the tables with joins that you want to modify.
In the Join tab of the Query Designer, select the join, and change the join conditions as needed.
You can also modify join types using FROM clause in the SQL SELECT command. For more information about SQL SELECT statements generated by the Query and View Designers, see How to: Create Queries (Visual FoxPro), How to: View and Edit SQL Statements for Views, and SELECT - SQL Command.
Removing Join Conditions
You can remove joins from between tables. The process for removing a join is similar for both the Query and View designers. The following procedure describes removing a join in the Query Designer.
To delete a join
Open the query that contains the tables with the join you want to remove.
In the Join tab of the Query Designer, select the join condition, and click Remove.
You can also select the join line that you want to remove between two tables on the Query or View Designer surface. On the Query menu, click Remove Join Condition.
See Also
Tasks
How to: Create Queries (Visual FoxPro)
How to: Query Multiple Tables and Views
How to: Run Queries (Visual FoxPro)