Hello,

I am a LLBLGen newbie, using version 1.0.2005.1 final.

I generate code using the adapter scenario for VS.net 2005, c#, SQL server.

Converting simple sql to using entityCollections, Predicates, etc wasn't too hard. But now I have a sql select statement that is a bit overwhelming me. Writing it in SQL was OK but i dont know where to start 'translating' it...

So, if anybody can give me some hints on how to use subquerys and how to use group by, i'd be very thankful. Links to examples would be very useful!

What i'm trying to do is this:

select top 5 od1.parentcode, od1.brand, od1.category, [product].name from orderdetail od1 left join [product] on od1.ProductCode = product.Code where od1.category = @category and product.[sale-price] is not null and od1.parentcode <> @parentcode and od1.orderID in (select od2.orderID from orderdetail od2 left join [order] on od2.orderID = [order].orderid where od2.parentcode = @parentcode ) group by od1.parentcode, od1.brand, od1.category, [product].name order by count(od1.parentcode) desc

(Hope i'm posting in the right group)

Thanks,

Steve

Hi,

Just add a GroupBy clause as parameter to your fetches. Before doing this, you've got to add fields (ResultsetFields) to the GroupBy, on which you'd like to GroupBy.

Doing subqueries is done more easily with version 2.0, but can also be executed with V1 by using the FieldCompareSetPredicate.

Good luck !

grtz, Danny

select top 5 od1.parentcode, od1.brand, od1.category, [product].name from orderdetail od1 left join [product] on od1.ProductCode = product.Code where od1.category = @category and product.[sale-price] is not null and od1.parentcode <> @parentcode and od1.orderID in (select od2.orderID from orderdetail od2 left join [order] on od2.orderID = [order].orderid where od2.parentcode = @parentcode ) group by od1.parentcode, od1.brand, od1.category, [product].name order by count(od1.parentcode) desc

You can use a dynamic list for the above query, with a FieldCompareSetPredicate for the IN clause.

And here is an example of implementing the left join:

RelationCollection relations= new RelationCollection();
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID, JoinHint.Left);

And you can find examples for the Grouping in the LLBLGen Pro manual: Using the generated code -> Adapter/SelfServicing -> Using TypedViews, TypedLists and Dynamic Lists -> Using GROUP BY and HAVING clauses

Thanks guys (en bedankt).