Hi, how do I do that?

Select name, address1 = (SELECT address from address where type=1 and clientId=c.clientId), address2 = (SELECT address from address where type=2 and clientId=c.clientId), FROM client c

or this

Select name, address1 = dbo.GetAddress1(c.clientId), address2 = dbo.GetAddress2(c.clientId) FROM client c

my real sql is a lot more complex but this is our goal here...

Or can I do that with LLBLGen pro??

SELECT DISTINCT clientName, address1 = a.address, address2 = b.address FROM client c LEFT OUTER JOIN address a ON c.clientId = a.clientId AND (a.type = 1 ) LEFT OUTER JOIN address b ON c.clientId = b.clientId AND (b.type = 2 OR b.type = 2)

tx for the help

Banane wrote:

Hi, how do I do that?

Select name, address1 = (SELECT address from address where type=1 and clientId=c.clientId), address2 = (SELECT address from address where type=2 and clientId=c.clientId), FROM client c

or this

Select name, address1 = dbo.GetAddress1(c.clientId), address2 = dbo.GetAddress2(c.clientId) FROM client c

my real sql is a lot more complex but this is our goal here...

What you want is not supported at the moment. Though you can most of the time rewrite such a query

Or can I do that with LLBLGen pro??

SELECT DISTINCT clientName, address1 = a.address, address2 = b.address FROM client c LEFT OUTER JOIN address a ON c.clientId = a.clientId AND (a.type = 1 ) LEFT OUTER JOIN address b ON c.clientId = b.clientId AND (b.type = 2 OR b.type = 2)

You can do that indeed. Create a dynamic list and when you add the relations to the relationcollection, use the Add() which accepts aliasses. Also when you add a relation to the relationcollection, set CustomFilter to the predicate you want to use as well in the join clause, like (a.type=1) and (b.Type=2 or b.type=2) (that's probably a typo )