You can specify a query on multiple tables in a QueryDataSet and JBuilder can resolve changes to such a DataSet. SQLResolver is able to resolve SQL queries that have more than one table reference. The metadata discovery will detect which table each column belongs to. The ability to resolve data from multiple tables focuses on these properties:
The tableName property may be different on the Columns belonging to one DataSet. The tableName and schemaName properties on a DataSet are null if this is a multiple table query. In this case the table names should be found per column.
The property resolveOrder is a String array that specifies the resolution order for multi-table resolution. INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order. If a table is removed from the list, the columns from that table will not be resolved.
There is exactly one record in T2 that corresponds to a record in T1 and vice versa. A relational database may have this layout for certain tables for either clarity or a limitation of the number of columns per table.
There can be several records in T2 that correspond to a record in T1, but only one record in T1 corresponds to a record in T2. Example: each customer can have several orders.
There is exactly one record in T2 that correspond to a record in T1, but several records in T1 may correspond to a record in T2. Example: each order may have a product id, which is associated with a product name in the products table. This is an example of a lookup expressed directly in SQL.
The most general case.
It is generally uninteresting to replicate the master fields for each detail record in the query. Instead, create a separate detail dataset, which allows correct resolution of the changes.
These should generally be handled using the lookup mechanism. However if the lookup is for display only (no editing of these fields), it could be handled as a multi-table query. For at least one column, mark the rowId property from the table with the lookup as not resolvable.
This table relationship arises very infrequently, and often it appears as a result of a specification error.
A query string may include table references and column references or aliases.
SELECT A.a1, A.a2, B.a3 FROM Table_Called_A AS A, Table_Called_B AS B
SELECT T1.NO AS NUMBER, T2.NO AS NR FROM T1, T2
The tableName, schemaName, and tableColumnName properties are set by the QueryProvider for QueryDataSets unless the metaDataUpdate property does not include metaDataUpdate.TABLENAME. These properties are added to the Inspector for a Column. These can then be modified in the Inspector when the metaDataUpdate property is turned off.
The StorageDataSet property resolveOrder is a String array that specifies the resolution order for multi-table resolution. INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order. If a table is removed from the list, the columns from that table will not be resolved.