PC SOFT

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Home | Sign in | English UK
  • 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
  • Limitations and notes
  • Specific joins
  • SQL Server syntax for joins
  • Advanced parameters
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
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:
  1. Select "Edit the join" from the popup menu of join (right mouse click). The description window of query joins is displayed.
  2. Click the "Add a join" button. A window used to define a new join is displayed.
  3. Specify the characteristics of join to add.
  4. Validate. The join is automatically added into the query.
This gives you the ability to create multiple joins between two data files.

Deleting a join from the current select query

To delete a join from the current select query:
  1. Select "Edit the join" from the popup menu of join (right mouse click). The description window of query joins is displayed.
  2. Select the join to delete.
  3. 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:
  1. Select "Edit the join" from the popup menu of join (right mouse click).
  2. The description window of query joins is displayed.
  3. Select the join to modify.
  4. Two options are available on this join, allowing you to define an external join ("Include also").
  5. Select the options corresponding to the requested result.
  6. 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:
  1. Select "Edit the join" from the popup menu of join (right mouse click).
  2. The description window of query joins is displayed.
  3. Select the join to edit.
  4. Click the "Edit the join conditions" button. The edit window of conditions is displayed.
  5. 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:
  1. Click on the button . The window for creating a condition is displayed.
  2. Select the item for which the condition will be created (1). Only the "key" items are proposed.
  3. Select the operation to perform in the list (2).
  4. 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.
  5. Validate. The condition is added to the previous condition.
  6. 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:
  1. Select "Edit the join" from the popup menu of join (right mouse click).
  2. The description window of query joins is displayed.
  3. Select the join to edit.
  4. Click the "Edit the join conditions" button. The edit window of conditions is displayed.
  5. 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:
  1. Click on the button . The window for creating a condition is displayed.
  2. Select the item for which the condition will be created (1). Only the "key" items are proposed.
  3. Select the operation to perform in the list (2).
  4. 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.
  5. Validate. The condition is added to the previous condition.
  6. 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:
  1. Select "Edit the join" from the popup menu of join (right mouse click).
  2. The description window of query joins is displayed.
  3. Select the join to edit.
  4. Click the "Edit the join conditions" button. The edit window of conditions is displayed.
  5. 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:
  1. Click on the button . The window for creating a condition is displayed.
  2. Select the item for which the condition will be created (1). Only the "key" items are proposed.
  3. Select the operation to perform in the list (2).
  4. 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.
  5. Validate. The condition is added to the previous condition.
  6. Don't forget to check the logical sequence of created conditions.
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 with the hQueryWithoutCorrection constant.
WINDEVWEBDEV - Server codeReports and QueriesWindowsWindows MobileNative Connectors (Native Accesses)

SQL Server syntax for joins

The SQL syntax for joins is supported (when editing an 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:
  1. Display the description window of query.
  2. Click the "Advanced" button.
  3. In the "Joins" tab, select the requested type of syntax.
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment