Linking SQL Queries

TECH TIP: Linking SQL Queries for Master/Detail Data

The following example shows two options for linking SQL queries to create a master/detail relationship.

In this example, we are using Delphi’s DBDemos data to create a Customer/Order relationship. Thus we wish to link the Orders detail to the Customer master.

I. Delphi Query Linking

a. Set the detail TQuery.DataSource property to point to the master query’s TDataSource component.

b. In the SQL “Where” clause for the detail query use a ‘:’ followed by the linking field name from the master:

Example:

select *
from orders
where orders.CustNo = :CustNo

Now each time the master record position changes, the detail query will automatically be refreshed with the correct result set.

II. RB DataPipeline Linking

a. Set the detail DataPipeline.MasterDataPipeline to point to the master DataPipeline.

b. Use the detail DataPipeline.MasterFieldLinks property to define the linking relationship

c. In the SQL for the detail, retrieve all records and sort them by the linking master field:

select *
from Orders
order by CustNo

Notes:

  1. Using RB DataPipeline, each query is executed only a single time – thus performance is much faster.
  2. RB Professional and Enterprise Editions include a visual Data environment for creating SQL queries, defining linking relationships, and creating Ask-At-Runtime parameters. Using the RB tools you could create the above linked queries in about 10 seconds.