Do you need a single EJB-QL that would do it. If so, you can try this
SELECT OBJECT(d) From component c, in (c.componenttypes) d where d.typeid = ?
Remember that if u are doing this from Component Bean, you can only do a EJB Select because the return value can only be of the bean in which it is defined. So you cant have a ejb finder doing this.
The usage pattern is that, I ussualy first find the Component(s) via some finder, and then execute component.getType(), which results in a single finder for Component and then a number of select ... from componentType where componentTypeId=? calls (1 for each component).
This is quite expensive. Since I know my usage pattern and each component has only 1 type component (n) -> (1) type, I would like to somehow execute the complex join sql, and set the CMR relation at the time the find is done.
Try having this in ur jbosscmp-jdbc.xml
<field-name>ur cmr field</field-name>
This should preload ur CMR fields along with ur find