PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • Overview
  • How to proceed?
  • 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
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 files of a select query
Overview
When several files are used in a query, WINDEV or WEBDEV automatically searches for the links described in the analysis between these files. These links are called joins in the query editor.
For example, the following query selects the order numbers of the orders placed by the customers who live in Paris. The ORDERS file and the CUSTOMER file are joined by the CustomerNum item. The join between these files was automatically defined by the query editor.
Note: The links between the files appear in the graphic representation of the query only if the two linked 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 proceed?

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" button. A window used to define a new join is displayed.
  3. Specify the characteristics of the join to add.
  4. Validate. The join is automatically added into the query.
This allows you to create multiple joins between two files.
Note: To display the description window of the joins from the description window of the 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:
  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" button. The join is automatically deleted. The joins between 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 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 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 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 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 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 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 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.

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:
  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 "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.
WINDEVWEBDEV - Server codeWINDEV MobileReports and QueriesWindowsWindows MobileNative Accesses

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