Query Builder

Query Builder will help you browse the database and create basic queries. You can select data from multiple tables/databases and Query Builder will create the SQL for you. Query Builder relies on FOREIGN KEY's in the database to automate the join's between multiple tables. Query Builder will be visible by default when a new report is created. For old reports simply push 'Show Query Builder'

Query Builder is designed to speed up report creation by automating the most tedious parts of query building: remembering tables and columns and relations between the columns.

Query Builder consists of four views of the database. The first view lists all databases visible to the user. The second view lists tables from the selected database and the third view lists columns from the selected table. The final view is for the user query columns.

Building a query

Select the columns you wish to be part of the query. You can select columns freely from different tables and databases.

Once the columns are selected, you can re-arrange the columns and select aggregate functions used by the query. When you have completed the column selection you can generate the SQL command by pushing either of the 'Generate SQL'-buttons. 'Generate SQL into the selection'-button will place the generated SQL code to the editor's current window at the insertion caret's position. 'Generate SQL into a new tab' will generate a new tab and place the code there.

Generated SQL:

select 	c.name as 'Name',
        p.name as 'Name',
        sum(pr.amount) as 'Amount'
from acre.Company c, acre.Product p, acre.Production pr
where c.id = pr.company_id and p.id = pr.product_id
group by c.name, p.name;

Please note that if your database has not defined the foreign key constraints no joins will be generated (where clause is 1=1). You can either create the foreign keys for tables or if you cannot change the database, define the relations to myDBR's 'mydbr_key_column_usage' table.