Implementing Inner Join using association

[Anurag Jamloki]

Hello,

Thanks a lot for the answer for my query 'Where condition in Joins', 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 

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 +"%");
objMRetrieveCriteria.WhereCondition.addSelectEqualTo("AccountContractAssociation.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>

[Richard Banks]

Hi Anurag,

Inner joins are not currently supported by the framework.

To get equivalent functionality in your retrieve criteria you should add a condition as follows:

objMRetrieveCriteria.WhereCondition.addSelectNotEqualTo("AccountContractAssociation.AccountContractId", nothing);

- Richard