Using an SQL JOIN statement
The sample report FLIGHT.REP (located in your dBASE Plus SAMPLES\FLEET directory) uses a single Query object whose SQL property contains an SQL JOIN statement linking the master table AIRCRAFT.DBF and the detail table FLIGHT.DBF. Both tables are indexed on a common field.
In the resulting report, each aircraft (stored in the master AIRCRAFT.DBF table) is displayed in the headerBand, followed by a list of flights for that aircraft (stored in the detail FLIGHT.DBF table) in the DetailBand.
This technique is usually faster and easier than adding and linking two Query objects. (However, in some cases, with BLOB fields, for example, it might be faster to use two Query objects.) You should also be aware that rowsets resulting from an SQL JOIN statement are read-only, and therefore cannot be edited.
By using two joined tables, you gain several advantages:
You can use the data as if it were all in one table.
Separately the tables can be more easily maintained.
If you have bitmaps, you need store them in only the master table, rather than duplicating the image in every row of the detail table.
This method does not require an index (although with indexes it is much faster).
To create a master-detail relationship by using an SQL JOIN statement,
Add a Query object to the design surface.
Select the Query object. In the Inspector, click the wrench tool beside the SQL property to display the SQL Property Builder.
Do one of the following:
Write an SQL JOIN query in the SQL Property Builder
Locate a query you’ve already written
Note: Including image fields slows performance.
If you’re designing a report, after the SQL property is set, choose Layout|Add Groups And Summaries. In the Groups And Summaries dialog box, all the fields from both tables appear in the Available Fields pane.
Select the field on which you want to group the detail rows.