Beginner needs help with EJB-QL and EJB relations.

[Julien Martin]

Hello,
Further to an earlier post, I have more questions relating to ejbs and ejb-ql.
I have three tables in a relational database:
*First table, called "establishments", contains two fiels "db_establishment_id" and "db_establishment_name".
Here is a sample:

db_establishment_id(pk) db_establishment_name
---------------------------------------------
1                       The black frog
2                       The king's head
3                       McDonalds

*Second table, called "categories", contains two fields "db_category_id" and "db_category_name"
Here is a sample:

db_category_id(pk)     db_category_name
-----------------------------------
1                      restaurant
2                      pub

*Third table, called "establishments_qualifiers", contains two fields "db_establishment_id" and "db_id_category"
Here is a sample:

db_establishment_id(pk,fk)    db_category_id(pk,fk)
--------------------------------------
1                             2
2                             2
2                             1
3                             1

These THREE tables map to TWO ejbs in a many-to-many bi-di relation (Is this the way it should be?) as follows:

<!--===========Establishment================ -->
<entity>
<ejb-name>EstablishmentEJB</ejb-name>
<local-home>com.softwareag.test_guide.establishment.EstablishmentLocalHome</local-home>
<local>com.softwareag.test_guide.establishment.EstablishmentLocal</local>
<ejb-class>com.softwareag.test_guide.establishment.EstablishmentBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
<reentrant>false</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Establishment</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>name</field-name>
</cmp-field>
<primkey-field>id</primkey-field>
<query>
<query-method>
<method-name>findEstablishmentsByCategory</method-name>
<method-params>
<!-- category -->
<method-param>java.lang.String</method-param>
<!-- name -->
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
SELECT OBJECT(es) FROM Establishment AS es, Category AS ca
WHERE 
es.id=ca.establishment_id
--I am stuck here
</ejb-ql>
</query>
</entity>
<!--===========Category================ -->
<entity>
<ejb-name>CategoryEJB</ejb-name>
<home>com.softwareag.test_guide.category.CategoryHome</home>
<remote>com.softwareag.test_guide.category.Category</remote>
<local-home>com.softwareag.test_guide.category.CategoryLocalHome</local-home>
<local>com.softwareag.test_guide.category.CategoryLocal</local>
<ejb-class>com.softwareag.test_guide.category.CategoryBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
<reentrant>false</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Category</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
<cmp-field>
<field-name>category</field-name>
</cmp-field>
<primkey-field>id</primkey-field>
</entity>
<!--===========Establishments-Categories ================-->
<ejb-relation>
<ejb-relation-name>Establishment-Category</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>Establishment-has-many-categories</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>EstablishmentEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>categories</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Category-has-many-establishments</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>CategoryEJB</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>establishments</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>

Ideally I would like an EstablishmentEJB instance that contains a list of categories. How do I do that? Do I need to modify the design of my application? What is the ejb-ql I need?
Thanks in advance,
Julien Martin.