Hello all,

I'm using MySQL 4.0.17 which doesn't support subselects. Given the following:

A.) I have a Users joined-subclass which contains a Set of PartyAttribute objects called partyAttributes that is inherited from its parent object, Party. This is mapped in the mapping file (in the Party declaration) as:


B.) A PartyAttribute object contains (amongst other things) an ivar called attributeValue - this is just a string and looks like this in the mapping declaration for PartyAttribute:


C.) A PartyAttribute object also contains an instance variable of type PartyTypeDefinition mapped thusly:


D.) A PartyTypeDefinition object contains a string attribute called attributeName mapped like this:


E.) And the last 2 important things... Each user has another instance variable of type PartyType which is inherited fomr the Party superclass:


And this PartyType object in turn has a Set call definitions which maps to a Set of PartyTypeDefinition objects:


So the relationship between these class is such that a "Type" is defined to have x number of attributes via the PartyTypeDefinition. Domain objects (e.g. User objects) are then assigned a particular "type". The values associated with the attributes of this type are then associated with the object, thereby enabling the object to have its own set of unique values for the attributes required by the type. For example:

1.) A PartyType object is defined as having a name "Administrator"
2.) A PartyTypeDefinition object is then defined for the "Administrator" type. It has a single attributeName of "Title"
3.) A User object is created of Type "Administrator" and it defines the "Title" attribute as having a value of "System Administrator"

So, finally my question is, since MySQL doesn't support subselects, what kind of a query do I need to create (using HQL if possible) that will allow me to search the database for Users whose Title='System Administrator."

A User has a Set of partyAttributes based on its type. Each attribute has a value particular to that user, however the PartyTypeDefinition object itself has one or more values associated with it (e.g. the "Title" attribute is assigned more than one party and each party has a different value - i.e. PartyAttribute.value).

It kinda looks like this:

User.partyAttributes.attributeValue (partyAttribtutes is a set of all the PartyAttribute objects)
For PartyAttribute object x its name (on our example "Title") is x.attributeDefinition.attributeName

For a PartyType object called type:

type.definition.attributeName
type.definition.attributName.attributeValues (where attributeValues is a Set of all the values mapped to a particular attribute and definition is - currently - a Set of all the definitions for a type. Thinking about it now it should be a one-to-one instead of a one-to-many, but that's the way its mapped right now.)

Anyway...

- I had executed the following query:


Only to have it throw a JDBC exception for bad syntax. Is it possible to do what I want to do using MySQL? I know this is a rather convulted example, so I tried to be as explicit as possible. Hopefully an answer will be short (and in the affirmative vein). Thanks!

--Brian[/b]

Probably something like:

Thanks gloeglm. That works as easily as I had hoped.