Complex query building

Top  Previous  Next

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: