"not in" versus joins

I have been fighting this HQL query for probably 8 hours in total now, where I have a table Entity and EntityAttribute. Every entity has zero or more attributes, so the EntityAttribute table has Entity_ID row in it. Attributes have a name and value, each in its own column. EntityAttribute is mapped as a map collection from Entity, with the "name" as the index. I want to select entity based on whether they do or do not have an attribute with a particular name and value, although I know in practice that those who attributes with the right name do have the rigth value always, at the moment anyway.

The end of the HQL looks something like this:


SELET DISTINCT e FROM entity
WHERE ...
... AND
'type' in indices(e.attributes)


'attributes ' is the collection of attributes.

I would have guessed this to work, but no. I eventually tried this in plain SQL, where it of course also does not work.

I also tried with this, for the other way around (exclusion):


SELET DISTINCT e FROM entity
WHERE ...
... AND
e.attributes['type'] != 'animation'


I can sort of understand this last construct being wrong. The correct way to do things is apparently to swap these two, basically use "not in" for exclusion and use "join" to include things, which is what e.attributes['type'] = 'animation' uses.