In the last example we built a report based on one table. Let's now look at query building where data comes from two tables.
Earlier we looked at a report working with groups. Let's build a query for this report using the query builder. We need to compose a query in SQL which will return data from both tables, with the data grouped on a specific condition. In our example the condition will be CustNo fields in both tables.
As in the previous example, create a new report and put a “TADOQuery” component on the page. Open the query editor and then the query builder.
Drag two tables to the work area – Customers and Orders. Both tables have a CustNo field which we will use to join them. Drag the CustNo field from one table to the other table to create a join between the two tables:
Now the fields to be displayed and the sort field need to be set. Check the "*" field in both tables and check the CustNo field in the Customer table. The selected fields appear in the field parameters list. Select the sort order for the CustNo field:
That is all that is needed to complete the Query. The SQL code looks like this:
