The query which is designed in this section retrieves data simultaneously from the Orders, Clients and Products tables. Since the contents of these tables is logically linked using the products and client keys, the basic SQL query will look something like (where ... is replaced by whatever fields are needed).
select .... from Client, Orders, Products where Orders.ClientID = Client.ClientID and Orders.ProductID = Products.ProductID |
To create a new query, go to the Query tab, open the appropriate branch, and double click the Create new query item. This brings up a new window; select a server database from the top-left combobox ( As for forms, if you select Self as the server then the query will access tables which are in the same server database as the query is stored in. ) , and the window will appear as shown below:
The left-hand listbox shows all tables for the selected server database. The top-right area will in due course show the tables used in the query and their relationship. The middle area is used to add expressions and critria such as filtering (SQL where terms) and ordering, while the lower area will show a skeleton of the SQL query that will be generated. Since this query requires all three tables, double-click in turn on each Client, Orders and Products (in that order). You can see that as changes are made, the SQL query text changes to match. With a bit of repositioning, the window should now look like:
If you need to set a table alias (so that the SQL will look something like select .... from Client C, ... then right-click in the table field list, and select Set Alias from the popup menu. This popup also contains Delete entry which can be use to remove a table.
The next stage is to add links between the table which specify the SQL query join conditions. First, there are two things to note. Currently, Rekall does not store any table relationship as other database front ends do, nor does it access any key/foreign key information that may be stored in the server database, so you need to add the links each time; a relationship editor will be added to a future release of Rekall. Secondly, Rekall currently only allows links where one end of the link is a primary key (you can see which ones these are, as Rekall marks them with a key icon).
Links are added simply by dragging and dropping, so drag and drop from Client.ClientID to Orders.ClientID, and from Orders.ProductID to Products.ProductID.
We can also add some filtering or ordering criteria. The Expression column should contain a valid SQL expression. The Usage column in the middle area can be set to one of the values below (if you click in it then it will change to a combobox):
Sort ascending: the expression is used to sort rows in ascending order.
Sort descending: the expression is used to sort rows in ascending order.
Where: the expression must be valid for use in an SQL where term.
Group by: the expression must be valid for use in an SQL group by term.
Having: the expression must be valid for use in an SQL having term.
Blank: this is simply a way of defining an expression which can be used when the query is accessed from a form or a table.
You can quickly enter columns into expressions (in the form table.column by dragging from a column in a table to an expression area; expressions can be deleted by right-clicking and selecting Delete. Again, the text in the lower panel will change to reflect changes made to the ordering and filtering criteria. The next screenshot shows the query with a few expessions added.
By default, when you display a query in data view, it will show a column of data for each column in each of the tables in the query. If, however, you have one or more data expressions (the last case in the list above) then in data view you will see only those expressions. If you want to see some specific expressions and also see all the columns, then add the expression *; this is analagous to the SQL select * from ... notation.
Unlike some other databases, it is not necessary to specify which columns (or, more generally, expressions) the query returns. When you use the query when designing a form or report, you will be able to select any column from any of the tables in the query (or any expression that uses them). Rekall will always construct the appropriate SQL query. However, as above, you can add arbitrary expressions which can be selected when designing a form or report; this may sometimes be convenient.
A query can be viewed in data mode just as a table. Although a query can be used in a form to retrieve data in a structured way (much like the linked tables in the Orders form - more on this later), the query viewer will "flatten" out the data, so that one row will be displayed for each row retrieved from the database. By the way, although you can switch a form between design view and data view without saving the form, you cannot do this with a report; if you try to switch to data view and the form as been modified, you will get a warning.
There are two caveats. Firstly, Rekall does not allow new rows to be inserted where the query contains more than a single table. This may be relaxed in a future release, but the semantics of doing this are not obvious (because of the join conditions between the tables). Secondly, it is not sensible to update values in columns which are used to relate the tables, since this might break the linkage displayed by the query viewer; these columns are displayed with gray backgrounds. ( Actually, Rekall will not allow you to update columns which show primary key values, however you can try to update the related key values (ie., in the above example, you cannot update the Client.ClientID column, but you can try to update Orders.ClientID. )
One final note. You should not make any assumptions about the order that the server database will return rows if you do not specify any ordering. In the example, you should not assume that all rows corresponding to a client will be returned contiguously.