Visual Query Builder

Introduction

If you are not an SQL guru and you have to compose database queries, the Visual Query Builder may be the right tool for you. The Visual Query Builder opens inside layouts, is saved inside the model file, and can be reopened from the Data Tools menu.

How to Start the Query Editor

Start the Query Editor by clicking the table header. Alternatively, start an empty editor and drag & drop a table from the layout. Alternatively, start an empty editor and drag & drop a table from the layout.
How to start the Visual Query Builder

Join Further Tables to the Query

You may join further tables to the query using foreign keys or virtual foreign keys. Virtual foreign keys can be created by column drag & drop in the layout and are saved to the model file.

Add further tables to the layout by clicking the small arrow icon near columns. Clicking the label on the foreign key line, you can switch to different join types: INNER JOIN, LEFT JOIN, EXISTS.

How to join further tables in the Visual Query Builder

Choose the Columns to Select

Choose the columns to select by ticking the column checkboxes.

How to select columns in the Visual Query Builder

Filter the Data

Data can be filtered by right-clicking any of the columns and choosing the 'Filter' option.

How to setup filters in the Visual Query Builder

Use Group By

Select the toggle button in the query builder menu to make the query use the GROUP BY clause. Then you can make use of aggregate functions by right-clicking any of the columns and choose Aggregate: MIN, MAX, SUM, AVG...

How to add Group By columns in the Visual Query Builder

Save Result to File

Similar to the SQL Editor, the result from the Result Pane can be saved to the file.

Dropping Query Editor

The Query Editor can be dropped by right-clicking the editor in the structure tree, under the 'Layouts'.

When closing an Query Editor, DbSchema will ask if you wish to preserve the editor in the design model ( close the editor but keep a copy in the design model, so it can be reopen at any time ), or close and drop from the design model as well.