How to select a first, second and third child record to a master

Davis wrote:

I have a master table and a child table 1 / n records. Need to list a child table field in each table record master, and only the first, second and third record of the child table.

Svein Erling Tysvaer answers:

I think your question can be best solved using EXECUTE BLOCK. I've never used it before (solved similar things using WITH RECURSIVE, but for this particular type of problem EXECUTE BLOCK is simpler and more intuitive), but try something like:

EXECUTE BLOCK RETURNS(CustomerId int, Name VarChar(255), ProductId1 integer,
   ProductId2 integer, ProductId3 integer)
as
declare i integer;
declare i2 integer;
begin
  for select customerid, name from MasterTable into :CustomerId, :Name
  do
  begin
    i = 1;
    ProductId1 = null;
    ProductId2 = null;
    ProductId3 = null;
    for select ProductId from ChildTable rows 1 to 3 into :i2 do
    begin
      if i = 1 then ProductId1 = i2;
      if i = 2 then ProductId2 = i2;
      if i = 3 then ProductId3 = i2;
      i = i + 1;
    end
    suspend;
  end
end

You really should use some field to order the records in the ChildTable (unless you want a random selection of records), but I didn't include any ORDER BY clause since you didn't specify how you wanted the fields ordered.