Where condition in Joins

[Anurag Jamloki]

Helo,

I am able to implement joins and assign where clause on primary table using association.

My problem is when I try to add where clause to secondary table it gives error.

Here is my code:

CMultiRetrieveCriteria objMRetrieveCriteria ;
CPersistentObject objAccountContract = new AccountContract();
objMRetrieveCriteria = new CMultiRetrieveCriteria((CPersistentObject)this);
objMRetrieveCriteria.addObjectToJoin(objAccountContract, this, "AccountContractAssociation");
objMRetrieveCriteria.WhereCondition.addSelectlike("AddressNumber", strAddressNumber +"%");

//Line which gives error
objMRetrieveCriteria.WhereCondition.addSelectEqualTo("ContractId", strContractId);

Here is my xml:

<class name="Account" table="Account" database="CTS_Client1">
        <attribute name="AccountId" column="AccountId" key="primary" proxy="true"  />
        <attribute name="ABALPH" column="ABALPH" />
        <attribute name="AddressNumber" column="AddressNumber" find="true"/>
        <attribute name="SyncroniseStatus" column="SyncroniseStatus" />
        <attribute name="Location" column="ALCTY1" />
        <attribute name="AccountContractAssociation" />
</class>

<class name="AccountContract" table="AccountContract" database="CTS_Client1">
        <attribute name="AccountContractId" column="AccountContractId" key="primary"/>
        <attribute name="AccountId" column="AccountId"  find="true"/>
        <attribute name="ContractId" column="ContractId" find="true"/>
        <attribute name="ProfileId" column="ProfileId" />
        <attribute name="SyncroniseStatus" column="SyncroniseStatus" />
</class>

<association fromClass="Account"
        toClass="AccountContract"
        cardinality="oneTomany"
        target="AccountContractAssociation"
        retrieveAutomatic="true"
        deleteAutomatic="false"
        saveAutomatic="false"
        inverse="true">
        <entry fromAttribute="AccountId" toAttribute="AccountId"/>
</association>

Thanks and Regards

Anurag

[Nobody/Anonymous]

Change your line of code to be

objMRetrieveCriteria.WhereCondition.addSelectEqualTo("AccountContractAssociation.ContractId", strContractId);

[Anurag Jamloki]

Thanks a lot, it worked fine.

The SQL genrated was of 'Left Join'.

Just a curusity question. How do I implement 'Inner Join' with same table structure and xml mapping. I've tried by changing cardinality from oneTomany to oneToOne. but it does not work.

Thanks and Regards

Anurag Jamloki