ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Editors / Query editor / Creating queries
  • Overview
  • How to?
  • Notes
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Creating a combining query
Overview
A combining query (corresponding SQL statement: UNION) is used to combine several select queries. Each select query must be based on data files of identical structure.
For example, select the customers whose last name starts with the letter 'A' and the prospects who live in Paris. To perform this selection, the two following queries must be combined:
  • selecting the customers whose last name starts with the letter 'A'. This query is based on the "Customer" data file.
  • selecting the prospects who live in London. This query is based on the "Prospect" data file.
The "Customer" and "Prospect" data files have the same structure in the analysis.
The records that are common to both data files ("Customer" and "Prospect") can:
  • appear once only in the result of the query (UNION operator)
  • appear twice in the result of the query (UNION ALL operator)
To combine queries, you must know:
  • the name of the queries to combine.
  • whether the common records must appear once or several times in the query result.
Remark: The items displayed in the result of the combining query correspond to the items of the first query run. Therefore, in our example, the people who are both customer and prospect, whose name starts with the letter 'A' and who live in London will only appear once in the result of the query.
Combining query
The query editor automatically generates the SQL code of this query.
To see the SQL code of a query:
  1. Open the context menu of the query graph.
  2. Select "SQL code".
You also have the ability to press the F2 key.
For example:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'

UNION

SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'
In order for the people who are both customer and prospect, whose last name starts with the letter 'A' and who live in London to appear several times in the query result, the SQL code must be:
SELECT
CUSTOMER.CustomerName AS CustomerName
FROM
CUSTOMER
WHERE
CUSTOMER.CustomerName LIKE 'A%'

UNION ALL

SELECT
PROSPECT.CustomerName AS CustomerName
FROM
PROSPECT
WHERE
PROSPECT.CITY = 'London'
How to?
To create a new combining query:
  1. Click New in the quick access buttons.
    • In the window that is displayed, click "Query".
    • The query creation wizard starts.
  2. Choose "Union" and validate.
  3. If your project is linked to no analysis, specify the analysis to which the query will be attached.
  4. The query description window appears.
  5. Enter the name and caption of the query.
  6. Select the different queries to combine (left box).
  7. Choose to keep or delete the records common to the combined queries ("Keep the duplicates/Delete the duplicates" button).
  8. Sort the result of the query if necessary ("Sort" button).
  9. Validate the query description window. The graphic representation of query is automatically displayed in the query editor.
Notes
  • To modify the characteristics of a query, go to the "Query" tab, "Edit" group and click "Description". For more details, see Description of a query.
  • To generate the SQL code corresponding to this query, select "SQL code" in the context menu of the query. If changes are made in the query description, these changes will be automatically reflected in the corresponding SQL code.
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 05/26/2022

Send a report | Local help