Gui Query Builder

Gui Query Builder - Gui Tab

Access this window from the Gui tab of the Gui Query Builder.

The Gui tab shows you:

the tables in your query. You can move these around, resize them etc.
the joins between the tables.

The primary keys of the tables are shown in blue when you click on the Key icon above the Gui window.

If a completely empty table-box is displayed, this means that AQT has been unable to find the columns for the table. This may happen because the table is held on a different server, or because the table-name has been qualified in a way that AQT is unable to interpret.

The menu icons are described in Gui Query Builder.

A strange bug - jumping windows

There is a peculiar bug in AQT and/or Windows NT. The tables may jump a few centimetres when you click on them. This problem can be prevented by changing an MS Windows setting. Go into Control Panel > Display and find the option Show Window Contents While Dragging (sometimes on the Effects tab). Switch this option off. We have only seen this behaviour in Windows NT.

Query Assistant

If you are new to databases you may find the Query Assistant useful. It gives hints and information about how to use the query builder – try it and see what it does. The Query Assistant icon is a question mark in a speech balloon.

Adding Tables to the Query

You can add tables to your query in the following ways:

Add another table icon	These first four methods take you into a dialog where you can specify the tables to be added to your query. It also allows you to see all tables related to your current query-tables.
double-click the background in the Gui window	If you add a table by either the double-click or right-click methods, the new table will be placed where your cursor is positioned.
right-click the background then select Add Table
Table > Add Another Table menu item (Ctrl-T)
Add All Related Tables icon	Automatically adds to the query all the tables related to the currently-selected table. First, click on one of the tables to select it (a message in the Status Bar at the bottom of the screen states which table is selected). For this option to work, your table must have some "known relationships" – either defined to the database (as primary/foreign keys) or set up as user-defined relationships. This option can take a few seconds to run. AQT queries the system tables to get the information on related tables. This seems to be particularly slow for Oracle.
Tables > Enter table name menu item	This option is useful if you know the name of the table you wish to add to your query, but do not wish to navigate through the normal Add Table dialog. Simply type the name of the table into the inputbox and it will be added to the query.

Joining Tables

If you use Add New Table > Add Known Relationship, or Add All Related Tables, your tables will be joined automatically by AQT.

To join tables manually:

click on the column you are joining from. You can select only a single column, which will then be highlighted in yellow. Information about the column will appear in the Status Bar at the bottom of the window.
holding the mouse button down, drag to the column you are joining to. As you do so, the message in the Status Bar will tell you about the join you are about to create. Pay particular note to the data-types. In general, these should be the same for both join columns. Release the mouse-button to create the join. Click on the background to not create the join.

Table join issues

Join only columns of same type and length

Generally, you should only join columns which are of the same type and length. If you try to join columns of different types, AQT will give you a warning message. You then have the option of aborting the join (in case you have made a mistake).

Multi-column joins

If your join is a multi-column join, you will need to repeat the join procedure for every column in the join. As you do so, you will get the message "This relationship has been added to the existing join".

You get only one join-line between the tables, even though there are multiple columns in the join. This is in contrast with some other products, which show one join-line for every pair of columns in the join. That can make the query's Gui very cluttered.

Tables relating to themselves

You cannot join a table to itself.

If a table has a relationship with itself (which may well be valid), you need to add the table twice to the query and join these. Even though these two tables have the same name, they will have different table-id/correlation names, so they can be referred to without ambiguity.

Multiple relationships between two tables

Similarly, there can only be one relationship between two tables. If TableA has two relationships to TableB (again, this is quite normal) you must create two instances of TableB in the query and join TableA to each of these.

Viewing or Amending Join Information

If you click on a join-line, the columns which are in the join will be highlighted. This is a very quick way to see the definition of join.

For other join options, right-click the join. You will shown a pop-up menu with options:

Join Details. Takes you into the Modify Join window, where you can view or modify the details of the join.
Delete Join.
Remember. The join will be saved as a user-defined relationship.
Join Type. This option allows you to select the join type (Inner join, Outer Join etc).

Removing a table from the query

You can do this by clicking on the Close (X) button on the table-window.

Selecting query columns

In the table-windows, the table-columns are shown and ticked to show if the column is in the query. You can include or exclude the column from the query by ticking / un-ticking the column check box.

The table-windows have their own menu options All and None to quickly select All or None of the columns for the query.

Many other attributes about the query column cannot be specified here, for instance the order in which the column appears in the query. To specify this and other options, click on the Columns tab.