How to create this SQL statement?

[Nobody/Anonymous]

SELECT   dbo.Orders.OrderID, dbo.Orders.CustomerID,
dbo.Customers.CompanyName, dbo.Customers.Address, dbo.Customers.City
FROM  dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
where dbo.Orders.OrderID='10643'
and dbo.Customers.CompanyName='Alfreds Futterkiste'

[Richard Banks]

Note: You must have an association from orders to customers in your XML mapping.  I am assuming the association is on the CustomerID attribute and that the target attribute is COrder.Customer.

dim rc as CMultiRetrieveCriteria
dim o as COrder
dim c as CCustomer
dim cursor as CCursor

rc = new CMultiRetrieveCriteria(o)
rc.addObjectToJoin(c,o,"Customer")
rc.WhereCondition.addSelectEqualTo("OrderID","10643")
rc.whereCondition.addSelectEqualTo("Customer.CompanyName","Alfreds Futterkiste")
cursor = rc.perform
while not cursor.eof
  o = new COrder
  c = new CCustomer
  cursor.loadObject(o)
  cursor.loadObject(c)
  ...
  cursor.nextCursor
end while

Hope this helps

- Richard.

[Nobody/Anonymous]

Hi Richard,

Thanks for the code.

I create the association and it works.
but it seems for the association "OnetoOne"
how about "OneToMany"

Thanks,

-Alan

[Richard Banks]

Hi Alan,

To use a one to many association you need to do two things

1.  Change the XML to use "OneToMany" instead of "OneToOne" in the association.

2.  Change the class definition to have the target object being a CPersistentCollection (ie a collection of persistent objects).  The reason for this is that if you add/remove objects from the collection the parent object will be marked as dirty automatically which saves you the hassle of doing it yourself.

The rest should be pretty much the same.

Using a multiretrieve criteria and navigating through a one to many association will work.  What you will find is that the doing a load object on record 2, 3 etc will return different objects on the "many" side, but the objects returned on the "one" side will look the same.

Try it and you'll see what I mean.

-Richard.