I'm trying to form the code that represents the query with left join on a filtered table. Like this:

select * from tableA left join (select * from tableB where name='needed') as tableTemp on tableA.key = tableTemp.key where ...

Is it possible to convert to LLBLGen code or rewrite to query to achive the desired result? One thing i can think of is to run two separate queries and to perform the left join in my own code, but that's not the most desirable solution for me.

Thanks in advance.

Could this be written as :

select * from tableA left join tableB on tableA.key = tableB.key 
where where tableB.name='needed' AND...

?

I don't think they are the same. I wanted to filter first, then left join.

So for example:

TableA:

Key Value

1 value1 2 value2 3 value3

TableB:

Key Name

1 needed 2 not needed

My query will produce 3 lines, and your query will produce 2 lines.

query is the same as:

select * from tableA left join tableB on tableA.key = tableB.key
AND name='needed'  
where ...

The AND clause can be added with the CustomFilter property of the EntityRelation. See "Custom filters for EntityRelations" in FIltering and sorting -> Advanced filtering.

Awesome! Thanks Otis.