Hi,

Just a simple question - how do I implement the following SQL in LLBL:

SELECT c2.CaseNo FROM [Case] c1 JOIN RelatedCase rc ON c1.CaseId = rc.CaseId JOIN [Case] c2 ON rc.CaseIdRelated = c2.CaseId WHERE c1.CaseNo = 1

I would like to fetch a typed list and have constructed the following code:

        Dim fields As New ResultsetFields(1)
        Dim tlist As New DataTable()
        Dim filter As IRelationPredicateBucket = New RelationPredicateBucket()
        fields.DefineField(CaseFieldIndex.CaseNo, 0, CaseFieldIndex.CaseNo.ToString())
        filter.Relations.Add(CaseEntity.Relations.RelatedCaseEntityUsingCaseId)
        filter.Relations.Add(RelatedCaseEntity.Relations.CaseEntityUsingRelatedCaseId)
        filter.PredicateExpression.AddWithAnd(CaseFields.CaseNo = CaseNo)
        Using daa As New DataAccessAdapter(True)
            daa.FetchTypedList(fields, tlist, filter)
            daa.CloseConnection()
        End Using

But how do I know on what Case table the where clause is added, and from which case table I actually get the values from?

I have the caseno's 1, 2 and 3 where cases 2 and 3 are related to 1. The code above only results in two 1's, which means I get the caseno from c1 (referring to the SQL above). I would like the result to be 2 and 3, fetched from c2 (again referring to the SQL above).

You should use an aliases for tables that are joined more than once. So you you should add the last relation with an alias to the relation collection.

filter.Relations.Add(RelatedCaseEntity.Relations.CaseEntityUsingRelatedCaseId, "C2")

And if you want to filter on this aliased entity (which is not you case), you should use an objectAlais for the predicate you use.

public FieldCompareValuePredicate(
   IEntityFieldCore field,
   IFieldPersistenceInfo persistenceInfo,
   ComparisonOperator comparisonOperator,
   object value,
   string objectAlias
);