I have a many-to-many association, and i want to retrieve all the item ids that are in a list of categories.

I tried this:

"select item.id from Item item where elements(item.categories) in (:categories)";

and then..
List categories =...; // list of category objects
query.setParameterList("categories", categories);

But I have an exception with the jdbc driver ( i am using jaybird jdbc driver with firebird 1.5).

"select item.id from Item item where elements(item.categories) in (1)";

This produces the following SQL:

select n.ID
from Items n where
(
(select an.ID // INSTEAD OF select an.CATEGORY_ID!!!!
from ITEM_CATEGORY an
where n.ID = an.ITEM_ID)
in(1)
)



ITEM_CATEGORY TABLE is defined as

ID | ITEM_ID | CATEGORY_ID

This seems work:

"select n.id from Category a left join a.items n where a in (:args)"