Hi, I'm using LLBLGen Pro v3.1 Final

Is there a way to define the join conditions for a relation? I have a query below that I ned to join a table using multiple condition.

SELECT ib.*
FROM LuItemBrand ib 
LEFT JOIN CatalogDetail cd ON cd.ItemBrandCd = ib.LookupValueCd OR cd.ItemBrandCd IS NULL
INNER JOIN Catalog c ON c.CatalogCd = cd.CatalogCd
INNER JOIN CustomerCatalog cc ON c.CatalogCd = cstc.CatalogCd
WHERE cc.CustomerCompanyCd = 'Blah'

Basically it is like pulling out all the brands in all the catalogs that a customer receives. NULL ItemBrandCd in the details represent a wildcard so I need to get those too. The default DISTINCT in llbl will help be elminate duplicates after.

I have the following code below but not sure how to do the multiple condition join.

IRelationPredicateBucket relation = new RelationPredicateBucket();
                relation.Relations.Add(LuItemBrandEntity.Relations.CatalogDetailEntityUsingItemBrandCd, JoinHint.Left);
                relation.Relations.Add(CatalogDetailEntity.Relations.CatalogEntityUsingCatalogCd);
                relation.Relations.Add(CatalogEntity.Relations.CustomerCatalogEntityUsingCatalogCd);

IPredicateExpression predicate = new PredicateExpression();
                predicate.AddWithAnd(CustomerCatalogFields.CustomerCompanyCd == "blah");

The left join obviously doesn't do what I need. How would I actually do this?

Thanks

How odd that I think I found the answer after posting this, when I've been stuck for hours before.

I see that you can replace the ON Claus by specifying it in the EntityRelation and there is a CustomFilterReplacesOnClause flag. The following code looks like it's working so far.

IPredicateExpression relationPredicate = new PredicateExpression();
                relationPredicate.Add(LuItemBrandFields.LookupValueCd == CatalogDetailFields.ItemBrandCd);
                relationPredicate.AddWithOr(new FieldCompareNullPredicate(CatalogDetailFields.ItemBrandCd, null));
                
IEntityRelation entityRelation = LuItemBrandEntity.Relations.CatalogDetailEntityUsingItemBrandCd;
                entityRelation.CustomFilterReplacesOnClause = true;
                entityRelation.CustomFilter = relationPredicate;

IRelationPredicateBucket relation = new RelationPredicateBucket();
                relation.Relations.Add(entityRelation, JoinHint.Left);
                relation.Relations.Add(CatalogDetailEntity.Relations.CatalogEntityUsingCatalogCd);
                relation.Relations.Add(CatalogEntity.Relations.CustomerCatalogEntityUsingCatalogCd);

IPredicateExpression predicate = new PredicateExpression();
                predicate.AddWithAnd(CustomerCatalogFields.CustomerCompanyCd == "blah");