LeftJoin Complex Expression doesn't give correct results

Chandler242

Hello,

I have following codes:

                var response = new Editor(db, "A_Run_PaveModelTree", "PavementID")
                    .Model<APaveModelTree>("A_Run_PaveModelTree")                                       
                    .Field(new Field("A_Run_PaveModelTree.ModelID")
                        .Options(new Options()
                            .Table("A_Model")
                            .Value("ID")
                            .Label("Description")                           
                        )
                    )
                    .Field(new Field("A_Run_PaveModelTree.TreeID")
                        .Options(new Options()
                            .Table("A_DecisionTree")
                            .Value("ID")
                            .Label("Description")                            
                        ))
                    .LeftJoin("A_Pavement", "A_Pavement.ID", "=", "A_Run_PaveModelTree.PavementID")
                    .LeftJoin("A_Model", "A_Model.ID=A_Run_PaveModelTree.ModelID AND (A_Model.ModelSetID=A_Run_PaveModelTree.ModelSetID)", "","")
                    .LeftJoin("A_DecisionTree", "A_DecisionTree.ID=A_Run_PaveModelTree.TreeID AND (A_DecisionTree.TreeSetID=A_Run_PaveModelTree.TreeSetID)", "","")
                    .Process(Request)
                    .Data();

For the two fields, the option still shows all the records in Tables A_Model and A_DecisionTree although my intent was to applied the two conditions in the second and third LeftJoin above. I wondered what I did wrong.

Any helps would be greatly appreciated.

YL

allan

Could you add .Debug(true) just before the .Process(...) call please? That will show us the SQL that is being executed.

That said, a complex join like this isn't supported at the moment - a VIEW is the way to go for anything beyond a simple join statement. At least for now .

Allan