I am attempting to create this basic query:

select a.col1, a.col2, a.col3, b.col4, c.col6 FROM a LEFT JOIN b ON a.col1=b.col1 and b.inactive_in = 0 LEFT JOIN c on c.col5 = b.col5 WHERE a.col1 = 'xxxxxxx'

Here is the code that I have so far:

ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(A.Col1, 0, "col1");
fields.DefineField(A.Col2, 1, "col2");
fields.DefineField(A.Col3, 2, "col3");
fields.DefineField(B.Col4, 3, "col4");

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(A.Relations.BUsingCol1, JoinHint.Left);

bucket.PredicateExpression.Add(A.Col1 == "xxxxxxx");

DataTable dynamicList = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, null);           
}
return dynamicList;

I believe I need to add:

bucket.Relations.Add(B.InactiveIn == 0);

or something like it, but it eludes me on what exactly I need to do.

The reason behind doing this is that I have rows in table A and rows in table B that relate to each other. But the rows in Table A may be "active" and the rows in Table B may be "inactive". I need to join the tables together and display rows in Table A even if their corresponding rows in table B are inactive. The query won't work if I put the b.inactive_in = 0 in the where clause. It must be part of the LEFT JOIN.

Please let me know if you can help.

Thanks...

For personalize the predicate on a join you can use CustomFilter for EntityRelations.