Hello,

i have a litle problem and i hope you can halp me.

struct of tables is:

table DIC1:

int id_dic1 string name1

table DIC2:

int id_dic2 string name2

table someTable:

int id_dic1 int id_dic2

i write query

SELECT st.ID_DIC1, st.ID_DIC2, dic2.NAME FROM SOME_TABLE AS st INNER JOIN DIC2 AS dic2 ON st.ID_DIC2 = dic2.ID_DIC2 WHERE (st.ID_DIC1 = @Param1)

UNION

SELECT NULL AS st_2.ID_DIC1, st_2.ID_DIC2, dic2.NAME FROM SOME_TABLE AS st_2 INNER JOIN DIC2 AS dic2 ON st_2.ID_DIC2 = dic2.ID_DIC2 WHERE (st_2.ID_DIC2 NOT IN (SELECT ID_DIC2 FROM SOME_TABLE AS st_1 WHERE (st_1.ID_DIC1 = @Param1)))

it should work like: - show all rows someTable where id_dic1 = @param1 [list_1] and - show rows someTable with id_dic1 = null - where id_dic2 (only ones - without duplicate) is not in list_1

how do it with llblgen ? (PredicateExpression or something)

Can't this query be simplified as:

SELECT * FROM DIC2 D2
LEFT JOIN 
(SELECT * FROM SOME_TABLE
WHERE ID_DIC1 = @Param1) ST
ON D2.id_dic2 = ST.id_dic2

Either way: the simplest solution is to have it in a database view, then map this view to a Typed View in LLBLGen Pro.

.. or do 2 fetches: first the list1, then the second one, into the same collection.

thx walaa yours query works yours inspire me and i find some else and it work the same and i think is quite simple:

SELECT * FROM DIC2 D2
LEFT JOIN SOME_TABLE ST
ON D2.id_dic2 = ST.id_dic2
and ST.ID_DIC1 = @param

but i can't write this like a view becouse in there is @param.

that why i need write a PredicateExpression to get data.

i try but in begin a have a problem i don't now what i sould write in param ctor entity collection EntityCollection list = new EntityCollection(??); becouse in result solud be fields from two table

and another question is how i write relation LEFT JONT .... ON ... AND

thank you for help

gqs wrote:

thx walaa yours query works yours inspire me and i find some else and it work the same and i think is quite simple:

SELECT * FROM DIC2 D2
LEFT JOIN SOME_TABLE ST
ON D2.id_dic2 = ST.id_dic2
and ST.ID_DIC1 = @param

but i can't write this like a view becouse in there is @param.

that why i need write a PredicateExpression to get data.

i try but in begin a have a problem i don't now what i sould write in param ctor entity collection EntityCollection list = new EntityCollection(??); becouse in result solud be fields from two table

You should create a dynamic list. Please see the documetation 'Using views and Lists' in adapter or selfservicing for more information about dynamic lists.

and another question is how i write relation LEFT JONT .... ON ... AND thank you for help

The additional predicate for the ON clause can be added using a custom filter on the relation. Please see 'Custom filters for EntityRelations' in Using the generated code -> Adapter -> Filtering and sorting -> Advanced filter usage -> Custom filters for EntityRelations.