Gui Query Builder - Table / Joins Tab

Access this window from the Table/Joins tab of the Gui Query Builder.

The information displayed in this window is very similar to the information displayed in the Gui window. However this window allows you to specify these options in a tabular interface, if you prefer this to the Gui interface. It is more useful for queries with large numbers of tables.

One option which can be specified here, and nowhere else, is the Table-Id, described below:

This window shows:

the tables in the query. You can add a table to the query with New Table and remove one with Delete.
the joins between the tables. You can add, modify, delete joins with the New, Modify and Delete buttons.

New table

This operates slightly differently in this window than it does in the Gui window. After adding a new table to the query, you will automatically be asked to specify a join for this table.

Table-Id

Tables can have a Table-Id, which is an abbreviation for the table name. The correct name for Table-Id is the "correlation name". You can use a short Table-Id to make the SQL less voluminous and easier to read.

If a table appears twice in the query, it is essential to have a Table-Id to distinguish which of the two instances of the table you are referring to.

AQT will by default generate a Table-Id for the tables. To change this, click in the Table-Id column and enter/amend the value. Then hit Enter or click elsewhere to effect the change.

The Options Tab allows you to set a number of options about how this Table-Id is generated.

Table Expression/Inline View

You can specify a Table Expression to be used in a query. You will use a Table Expression when your data is to come from an SQL statement, rather than a table. Table Expressions are also known as Inline Views or sub-queries.

An example of a query which uses a Table Expression is

SELECT * FROM

Customers a,

(SELECT CustomerID, count(*) as num FROM Orders GROUP BY CustomerID) b

WHERE a.CustomerID=b.CustomerID

There are two "tables" in this query:

the first table is Customers
the second table is (SELECT CustomerID, count(*) AS num FROM Orders GROUP BY CustomerID)

This second of these is a Table Expression. Most databases support the use of Table Expressions, which provide a mechanism for developing powerful queries.

To specify a Table Expression, click on New Table Expression. Enter the SQL text for the table expression. Future releases of AQT will allow Table Expressions to be built by the Gui Query Builder.

The table will now appear in the query with the name (expression). If you create more than one Table Expression, the Table-Ids will differentiate them.

You can amend a table expression using the Edit Table Expression feature. However be cautious when you do this. If you remove or change the columns in your query, AQT can get confused – as yet it doesn't tidy unused column names from the query.

Identifying Table Expressions

A Table Expression must generally have a Table-Id. AQT will normally generate one unless you have set the option not to use Table-Id (Options > Table ID > no table id), or if it is the only table in the query.

Any generated or summary columns in your Table Expression should be given names with the AS clause.

Invalid	no proper name for second column, difficult to reference	`SELECT CustomerID, count(*) FROM Orders GROUP BY CustomerID`
Valid	`AS` clause gives name to column	`SELECT CustomerID, count(*) AS CustCount FROM Orders GROUP BY CustomerID`

In order to determine the columns in the Table Expression, AQT does a "prepare" on the SQL for the Table Expression. Some databases do not support a standalone prepare, and may run the SQL; this might cause a delay when you enter or change a Table Expression.