Thanks for the replies. Unfortunately, I won't be able to use the NHIbernate solution as I'm required to keep the solution JPA compatible.
lonny27, I like your solution, but the problem is for every new category I add, another self-join must be made - and there can be many categories. I had actually worked out a similar solution with pure SQL.
But, I think I found a solution that I'll settle with. It goes like this:
Code:
select c.itemId from Category c where c.kind = 'apples' or c.kind = 'oranges' group by c.itemId having count(c.itemId) >= 2
This works fine, except in the case of duplicate categories. If an item had, say, 'apples' twice but no 'oranges' it would show up in the result. While this is possible, it is unlikely.