CMR and EJBQL

Hi,
Can somebody please give me some advice on how to construct ejbql queries when using cmr fields?
For example: I have two tables table A which has got 4 fields in it a b c and d. fields A nd B are primary key fields in table A. And Table B which has 5 fields in it K, E, F , G and H.
Fields G, and H in table B are foreign key fields to Table A.
Table A has a one to many relationship with Table B. (Table B has a one to one with Table A)
If i search in Table B with integer values for E and F it returns me a collection of rows. Each row is a entity local. The values for G and H in these rows map to the primary key fields in Table A which are A and B.
For every result in the collection I want to search table A with the values in G and H.
In sql i would have to do a join between the two tables. How would I do this using EJB_QL?
i think it can be done using the select method rather than a finder method but am not sure how to implement it.

Thanks in advance

Hi,
Assume that the Abstract schema name for Table A and Table B are SchemaA and SchemaB
SELECT Object(a) FROM SchemaA AS a, IN(a.SchemaB) AS b
WHERE b.E = ?1 AND b.F = ?2
I hope that this query will work.
Please let me know, if u need more info.
Sathish

"Table A has a one to many relationship with Table B. (Table B has a one to one with Table A)"
Is this right?

Its correct.
Think of the relation between a Person and Phone numbers.
A person can have many Phone numbers(Office, Residence..)
But a phone number can be associated only with one person.

Thanks for the reply!
Would I put this query into the ejb jar descriptor enclosed within the entity for table A or enclosed within the entity tags for table B. I know that the ejbql will be wtihin a query tag.

Also would this be a ejb select method or is it just a normal finder method?

Hello again,
I tried the query by putting it in as a finder in the ejb jar xml an extract from the ejb jar file is below:
<query>
<query-method>
<method-name>findStoresInStoreGroups</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
<method-param>java.lang.Integer</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
</ejb-ql>
</query>
and i got the following error when running ejbc? any ideas?
[java] [EJBCompiler] : Compliance Checker said bean was compliant
[java] ERROR: Error from ejbc: Error while reading 'META-INF/weblogic-cmp-rdbms-jar.xml'. The error was:
[java]
[java] weblogic.ejb20.cmp.rdbms.finders.IllegalExpressionException:
[java] While trying to process Query
[java] Method Name: findStoresInStoreGroups
[java] Parameter Types: (java.lang.Integer, java.lang.Integer)
[java] EJB Query: SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
[java] )
[java] Could not parse EJB QL expression: SELECT OBJECT(locn) FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2
[java] weblogic.ejb20.ejbc.EJBCException: line 1: unexpected token: WHERE
[java] Error in FROM clause, there may be a problem with the FROM Clause.
[java] Check that the Range Variable Declarations and the Collection Member Declarations are correct
[java] and that no EJB QL keywords are being used as:
[java] Range Variable names,
[java] Collection Member names,
[java] Abstract Schema names,

Originally posted by Alex:
SELECT OBJECT(locn)
FROM LocationEntity AS locn, IN(locn.StoreGroupMemberEntity) AS group
WHERE group.locationIdGroup = ?1 AND group.locationTypeIdGroup = ?2

Entity bean attributes, following standard JavaBeans naming standards, begin with a lowercase letter. The accessor "getStoreGroupMemberEntity()" results in a bean attribute named "storeGroupMemberEntity."