Joins between the data files of a select query

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Version:

Home | Sign in | English

Overview
How to?
Adding a join into the current select query
Deleting a join from the current select query
Notion of external join in the current select query
Editing the conditions of a join in the current select query
Adding a condition
Reinitializing the query joins
Limitations and notes
Specific joins
SQL Server syntax for joins
Advanced parameters

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

Joins between the data files of a select query

Overview

When several data files are used in a query, the query editor automatically searches for the links between these files described in the analysis. These links are called joins in the query editor.

For example, the following query selects the numbers of the orders placed by the customers who live in Paris. The ORDERS and CUSTOMER data files are joined by the CustomerNum item. The query editor automatically defines the joins between these two data files.

Note: The links between the data files appear in the query graphic representation only if the two linked data files are used by the query.

By default, the joins are automatically created by the query editor. However, these joins may not correspond to the requested result. Therefore, you have the ability to add or delete joins. These operations are described in the following paragraphs.

How to?

Adding a join into the current select query

To add a join into the current select query:

Select "Edit the join" from the popup menu of join (right mouse click). The description window of query joins is displayed.
Click the "Add" button. A window used to define a new join is displayed.Click the "Add a join" button. A window used to define a new join is displayed.
Specify the characteristics of join to add.
Validate. The join is automatically added into the query.

This gives you the ability to create multiple joins between two data files. Note: To display the description window of joins from the description window of query:

Click the "Advanced" button.
Display the "Joins" tab.
Click the "Edit the joins" button. The description window of joins is displayed.

Deleting a join from the current select query

To delete a join from the current select query:

Select "Edit the join" from the popup menu of join (right mouse click). The description window of query joins is displayed.
Select the join to delete.
Click the "Delete" button. The join is automatically deleted. The joins between data files are automatically optimized in order for the query to operate properly.Click the "Delete the join" button. The join is automatically deleted. The joins between data files are automatically optimized in order for the query to operate properly.

Notion of external join in the current select query

When calculating a join between two data files ("Parent" file and "Child" path), only the records with a correspondence in the two data files are kept by default. However, the notion of "External join" allows you define a rule to include:

the records of "Parent" file without correspondence in the "Child" file.
the records of "Child" file without correspondence in the "Parent" file.

To define an external join in the current select query:

Select "Edit the join" from the popup menu of join (right mouse click).
The description window of query joins is displayed.
Select the join to modify.
Two options are available on this join, allowing you to define an external join ("Include also").
Select the options corresponding to the requested result.
Validate.

Versions 22 and later

Editing the conditions of a join in the current select query

To edit the conditions of a join in the current select query:

Select "Edit the join" from the popup menu of join (right mouse click).
The description window of query joins is displayed.
Select the join to edit.
Click the "Edit the conditions" button. The edit window of conditions is displayed.Click the "Edit the join conditions" button. The edit window of conditions is displayed.
You can:
- Add a new condition.
- Modify a condition.
- Delete a condition.
- Define the logical sequence of conditions via operators (AND, OR) and brackets ( and ). You must use the number representing the condition in the formula. For example, with 2 conditions, the formula can be: "1 OR 2".

Adding a condition

To add a condition:

Click on the button. The window for creating a condition is displayed.
Select the item for which the condition will be created (1). Only the "key" items are proposed.
Select the operation to perform in the list (2).
Select the type of element to compare for the operation to perform (3):
- the value: this option is selected for a static value in the query.
- the parameter: this option is selected when passing parameters during the query execution.
- the item: this option is selected when performiing a comparison with an item found in another query file. Only the "key" items are proposed.
Validate. The condition is added to the previous condition.
Don't forget to check the logical sequence of created conditions.

New in version 22

Editing the conditions of a join in the current select query

To edit the conditions of a join in the current select query:

Select "Edit the join" from the popup menu of join (right mouse click).
The description window of query joins is displayed.
Select the join to edit.
Click the "Edit the conditions" button. The edit window of conditions is displayed.Click the "Edit the join conditions" button. The edit window of conditions is displayed.
You can:
- Add a new condition.
- Modify a condition.
- Delete a condition.
- Define the logical sequence of conditions via operators (AND, OR) and brackets ( and ). You must use the number representing the condition in the formula. For example, with 2 conditions, the formula can be: "1 OR 2".

Adding a condition

To add a condition:

Click on the button. The window for creating a condition is displayed.
Select the item for which the condition will be created (1). Only the "key" items are proposed.
Select the operation to perform in the list (2).
Select the type of element to compare for the operation to perform (3):
- the value: this option is selected for a static value in the query.
- the parameter: this option is selected when passing parameters during the query execution.
- the item: this option is selected when performiing a comparison with an item found in another query file. Only the "key" items are proposed.
Validate. The condition is added to the previous condition.
Don't forget to check the logical sequence of created conditions.

Editing the conditions of a join in the current select query

To edit the conditions of a join in the current select query:

Select "Edit the join" from the popup menu of join (right mouse click).
The description window of query joins is displayed.
Select the join to edit.
Click the "Edit the conditions" button. The edit window of conditions is displayed.Click the "Edit the join conditions" button. The edit window of conditions is displayed.
You can:
- Add a new condition.
- Modify a condition.
- Delete a condition.
- Define the logical sequence of conditions via operators (AND, OR) and brackets ( and ). You must use the number representing the condition in the formula. For example, with 2 conditions, the formula can be: "1 OR 2".

Adding a condition

To add a condition:

Click on the button. The window for creating a condition is displayed.
Select the item for which the condition will be created (1). Only the "key" items are proposed.
Select the operation to perform in the list (2).
Select the type of element to compare for the operation to perform (3):
- the value: this option is selected for a static value in the query.
- the parameter: this option is selected when passing parameters during the query execution.
- the item: this option is selected when performiing a comparison with an item found in another query file. Only the "key" items are proposed.
Validate. The condition is added to the previous condition.
Don't forget to check the logical sequence of created conditions.

Reinitializing the query joins

Reinitializing the joins is used to delete all the modifications made to the joins since the query was created. The query editor automatically re-creates optimized joins.

To reinitialize the joins of current query:

Select "Edit the join" from the popup menu of join (right mouse click). The description window of query joins is displayed.
Click the "Reinitialize" button. The joins that were manually created are deleted.

Limitations and notes

Specific joins

The joins such as (A Join B on x=y) Join C on y=z are not supported.

To run queries with such joins, you must use HExecuteQuery or HExecuteSQLQuery associated with the hQueryWithoutCorrection constant.

SQL Server syntax for joins

The SQL syntax for joins is supported (when editing a SQL query and when running it). To run these queries, you have the ability to use HExecuteQuery or HExecuteSQLQuery.

Advanced parameters

By default, the joins use the ISO syntax (INNER JOIN / LEFT OUTER JOIN / RIGHT OUTER JOIN / FULL JOIN). You also have the ability to use the Oracle syntax of joins. However, this syntax compatible with Oracle 7 is not recommended.

To use the Oracle syntax:

Display the description window of query.
Click the "Advanced" button.
In the "Joins" tab, select the requested type of syntax.