Re: Multiple join condition in the on clause

Posts   
 
    
Krish
User
Posts: 91
Joined: 02-Jan-2008
# Posted on: 09-Feb-2018 02:48:33   

Hi, How do you do a query like the following in querySpec syntax please:

SELECT * From table1 t1 left join table2 t2 on t1.checkId = t2.checkId and t1.verificationId = t2.verificationId where t1.eNum = '1234' and t1.checkId = 2

i.e. there is more than one predicate in the 'on' clause

I am using LLBLGen Pro 5.1.1 RTM, Adapter, C#.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Feb-2018 08:23:44   

Hi Krish,

This is an example using the LLBLGen API:

var customFilter = new PredicateExpression();
customFilter.Add(OrderFields.ShipCountry.Equal("Mexico"));
// ... 
relationsToUse.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
                .CustomFilter = customFilter;
// ...

And here is one using QuerySpec

// QuerySpec alternative (qf is QueryFactory instance). It lets you 
// define the ON clause in full.
...
.From(QueryTarget.InnerJoin(qf.Order)
    .On((CustomerFields.CustomerId==OrderFields.CustomerId)
        .And(OrderFields.ShipCountry=="Mexico")));

Those examples were extracted from the documentation: Advance filtering -> Custom filters for Entity Relations.

Hope that helps sunglasses

David Elizondo | LLBLGen Support Team
Krish
User
Posts: 91
Joined: 02-Jan-2008
# Posted on: 10-Feb-2018 08:25:34   

Thank you David.

Sorry didnt mention dynamic query.

I am using dynamic query where QueryTarget can only be used in subsequential calls. ( http://www.llblgen.com/documentation/5.2/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_joins.htm )

I want to use more than one predicate in the 'on' clause in the very first join.

How do you do this please?

Krish
User
Posts: 91
Joined: 02-Jan-2008
# Posted on: 10-Feb-2018 22:53:21   

Hi David, Sorry I have got it working. It was a simple error in my syntax. I was not putting the left parentheses in the right place and so did not get the "And" option. It did not matter it was a dynamic query.