SELECT *
FROM Case
LEFT JOIN Stage ON Stage.TransactionTypeId = Case.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID

I can't figure out how to make that second join happen?

I am not 100% sure but I don't think you can't achieve that in designer.

renesisx wrote:

SELECT *
FROM Case
LEFT JOIN Stage ON Stage.TransactionTypeId = Case.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID

I can't figure out how to make that second join happen?

Can you explain the logic of the query in English? i.e. something like "Give me a list of Cases and their related Stages, if they share the same transaction ID" That's my rough approximation of the query, but I'm not sure your SQL answers that question. Again I might have gotten the question wrong to begin with, and I also don't have any data.

Chester wrote:

Can you explain the logic of the query in English?

Sure,

The actual query would look like this (WHERE clause is added at runtime):

SELECT *
FROM Case
LEFT JOIN Stage ON Stage.TransactionTypeId = Case.TransactionTypeId
LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID
AND CaseStageStatus.StageID = Stage.StageID
WHERE Case.CaseID = 2

(ignore the fact I'm using * - I just didn't want to put a huge list of fields here to complicate things)

Basically, each Case has a TransactionType, let's say "Purchasing" in this example.

"Purchasing" has a fixed list of 10 stages which are defined by 10 of the rows in the Stage table. (One stage might be "Send Invoice To Customer")

CaseStageStatus has one row for each Stage that is completed in a Case. So, if "Send Invoice To Customer" is complete, then there will be a row in CaseStageStatus with the Case.CaseID and the Stage.StageID.

What I'm trying to produce is a list of all 10 stages for a particular case, with a tick beside each one that is complete. It must show all the stages and not just the ones that are complete. It's basically like a To Do list for a case, so you can see how many stages still need to be done.

Can this be done in the designer? I'm not sure it can, although I see a comment in the forum from like 2004 saying it was being added. Maybe there is another way to write the query so I can build it in the designer?

If I can't do it in the designer, how do I do it in code?

Thanks.

I've tried this, but the results are wrong:

        Dim fields As New ResultsetFields(6)
        fields.DefineField(StageFields.Description, 0, "Description", "Stage")
        fields.DefineField(CaseStageStatusFields.CaseID, 1, "CaseID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.StageID, 2, "StageID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.CaseStageStatusID, 3, "CaseStageStatusID", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.Completed, 4, "Completed", "CaseStageStatus")
        fields.DefineField(CaseStageStatusFields.ExpectedCompletion, 5, "ExpectedCompletion", "CaseStageStatus")

        Dim relations As IRelationCollection = New RelationCollection()
        relations.Add(CaseEntity.Relations.TransactionTypeEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(TransactionTypeEntity.Relations.StageEntityUsingTransactionTypeID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.CaseEntityUsingCaseID, JoinHint.Left)
        relations.Add(CaseStageStatusEntity.Relations.StageEntityUsingStageID, JoinHint.Left)

        Dim objCaseSelectFilter As New PredicateExpression()
        objCaseSelectFilter.Add(CaseFields.CaseID = 2)

        Dim data As New System.Data.DataTable
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, data, 0, Nothing, objCaseSelectFilter, relations, False, Nothing, Nothing, 0, 0)

So, the first row is correct, e.g.

"Contract received" | 2 | 1 | 3 | NULL | 28 / 10 / 2006

The second row is:

"Invoice sent" | 2 | 1 | 3 | NULL | 28 / 10 / 2006

Where it should be:

"Invoice sent" | NULL | NULL | NULL | NULL | NULL

Any support people around today who can help?

I need to get this working by tomorrow...!

Wild guess: relations.Add(CaseStageStatusEntity.Relations.StageEntityUsingStageID, JoinHint.Left) should be relations.Add(CaseStageStatusEntity.Relations.StageEntityUsingStageID, JoinHint.Right)

as you otherwise will get CaseStage LEFT JOIN Stage, but you want nulls in the casestage fields even when there's a stage field.

Hey Otis,

Same output I'm afraid.

I'm trying to duplicate this line with those last 2 "relation.Add" statements.

LEFT JOIN CaseStageStatus ON CaseStageStatus.CaseID = Case.CaseID AND CaseStageStatus.StageID = Stage.StageID

I think you should be fine using a custom filter with your two predicates and setting the

CustomFilterReplacesOnClause

property to true

Jessynoo wrote:

I think you should be fine using a custom filter with your two predicates and setting the

CustomFilterReplacesOnClause

property to true

Please humour me... where am I setting this property?

renesisx wrote:

Jessynoo wrote:

I think you should be fine using a custom filter with your two predicates and setting the

CustomFilterReplacesOnClause

property to true

Please humour me... where am I setting this property?

On the entityrelation object you're adding to the relationcollection? CustomFilterReplacesOnClause is the flag used to specify to LLBLGen Pro that the specified filter set with CustomFilter is to be used as the ON clause.

This thread's discussion is actually done in two threads now. This is confusing. So please inform us which one of the threads is helpful so we can close the other.