MySQL Query Browser, one of the open source MySQL GUI tools from MySQL AB, is used for building MySQL database queries visually. In MySQL Query Browser, you build database queries using just your mouse—click, drag and drop!
MySQL Query Browser has plenty of visual query building functions and features. This article shows two examples, building Join and Master-detail queries. These examples will demonstrate some of these functions and features.
Join Query
A pop-up query toolbar will appear when you drag a table or column from the Object Browser’s Schemata tab to the Query Area. You drop the table or column on the pop-up query toolbar’s button to build your query.
The following example demonstrates the use of the pop-up query toolbar to build a join query that involves three tables and two types of join (equi and left outer).
Drag and drop the product table from the Schemata to Add Table(s) button.
A SELECT query on the product table is written in the Query Area.
Drag and drop the item table from Schemata to the JOIN Table(s) button on the Pop-up Query Toolbar.
The two tables are joined on the foreign-key, product_code.
If no foreign-key relationship exists, the drag and drop won’t have any effect.
Drag and drop the order table from Schemata to the LEFT OUTER JOIN button on the Pop-up Query Toolbar.
Maximize query area by pressing F11. You get a larger query area, and your lines are sequentially numbered (for easier identification). Move the FROM clause to its next line, by putting your cursor just before the FROM word and press Enter.
Similarly, move the ON clause to its next line.
Now, you can see all lines completely, and that the item table is left join to the order table on their foreign-key relationship column, the order_number column.
As of now our query is SELECT *, i.e. selecting all columns from all tables. Let’s now select the columns we’d like to show at the query’s output. For example, drag and drop the order_number from the item table, product_name from the product table, and then quantity from the item table. (If necessary, expand the table folders to see their columns).
The sequence of the selecting the columns is reflected in the SELECT clause (from left to right).
Note that you can’t select column from the left join of the order table (if you try, nothing will happen)
Next, add an additional condition. Drag and drop the amount column on the WHERE button in the Pop-up Query Toolbar.
The column is added, with an AND, in the WHERE clause of the query. Type in its condition value, for example, > 1000.
To finalize our query, drag and drop product_name on the ORDER button, and then, order_number (from item table, not order table) on the GROUP button. You’ll see that the GROUP BY and ORDER clauses are ordered correctly, i.e. the GROUP BY clause first before the ORDER BY, regardless of your drag & drop sequence.
To test your query, click the Execute button.
Your query should run without any error, and display its output in the query area (below the query).
