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 consist of four views of the database. The first view lists all databases visible to the user. The second view lists tables in selected database and third view lists columns in selected table. The final view is for the user query columns.
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 selection'-button will place the generated SQL code to the editor's current window at the insertion caret's position. 'Generate SQL into new tab' will generate a new tab and place the code there.
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 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.