Hi, I am having a few problems with queries on an MS Access database when I use object relationships to access related tables. I am using Cayenne 3.1 M2.
My case is very simple. Let's say I have two tables A and B where A has a column col containing a primary key of B. Given an object a from table A with colValue, I want to access the related record of B using a.getB(). Cayenne creates a select query that joins A and B like this: SELECT DISTINCT t0.columns... FROM B t0 *JOIN* A t1 ON (t0.pk = t1.col) WHERE t1.pk = ? [bind: 1-> a.pkValue] The first thing is that this produces a syntax error as the simple *JOIN *keyword is not valid in MS Access. They want an explicit *INNER JOIN*. But secondly I wonder why Cayenne needs a JOIN in this case anyway, since it is of no use at all. Why does it not use something like: SELECT t0.columns... FROM B t0 WHERE t0.pk = ? [bind 1-> a.colValue] My workaround is to manually create a SELECT query to access B without joining it A: Expression e = ExpressionFactory.matchExp(B.primaryKeyProperty, A.colValue); SelectQuery q = new SelectQuery(B.class, e); This works but of course it would be nicer to use the getter function A.getB(). Is there a possibility to either avoid the JOIN or tell Cayenne to use INNER JOIN instead of JOIN ? Please let me know in case you need more detailed info. Thanks for any help. Wernke
