I know it must be easy but I haven't been able to track down the syntax to include a field from a derived table in the result set. E.g C.Code in the example below:

SELECT A.ID, C.Code FROM A
INNER JOIN ( SELECT ID, Code FROM B ) C ON C.ID = A.ID

(2.6, Adapter, lgp API)

Here is an example. Supposeyou want this query:

SELECT 
    [LPA_O2].[OrderID] AS [OrderId], 
    [LPA_O1].[Total] 

FROM 
( 
    (
        SELECT 
            [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId], 
            SUM([Northwind].[dbo].[Order Details].[Quantity] * [Northwind].[dbo].[Order Details].[UnitPrice]) AS [Total] 
        FROM [Northwind].[dbo].[Order Details]  
        GROUP BY [Northwind].[dbo].[Order Details].[OrderID]
    ) [LPA_O1]  

    INNER JOIN [Northwind].[dbo].[Orders] [LPA_O2] ON  [LPA_O1].[OrderId] = [LPA_O2].[OrderID]
) 

WHERE ( ( [LPA_O1].[Total] > @Total1))

Parameter: @Total1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5000.

... this is the c# code that generated it:

// first specify the elements in the derived table select (which is a dyn. list)
ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(OrderDetailFields.OrderId, 0);
dtFields.DefineField(new EntityField2("Total",
             (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice), AggregateFunction.Sum), 1);
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]);
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "OrderDetailTotals", null, dtGroupBy);

// then specify the relation. 
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                    EntityType.OrderEntity, "O",
                   (new EntityField2(OrderDetailFieldIndex.OrderId.ToString(), "OrderDetailTotals", typeof(int)) ==
                           OrderFields.OrderId.SetObjectAlias("O")));

// then specify the rest of the query elements (add the relation, and some filter)
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(relation);
filter.SelectListAlias = "O";
filter.PredicateExpression.Add(new EntityField2("Total", "OrderDetailTotals", typeof(int)) > 5000);

// now setup the resulset fields. As we want to fetch fields from inside the DT, we must
// use DynamicList.
EntityFields2 fields = new EntityFields2(2);
fields.DefineField(OrderFields.OrderId, 0);         

// this is how you can specify fields from inside the DerivedTable
fields.DefineField(new EntityField2("Total", "OrderDetailTotals", typeof(int)), 1);


// then fetch the data
DataTable resuts = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, resuts, filter, true);           
}

This is the relevant part, where you specify the field with the alias:

fields.DefineField(new EntityField2("Total", "OrderDetailTotals", typeof(int)), 1);

Please let us know whether you need further help on this