Hello, much appreciated to anyone that can help me with this query.

I have a relationship that looks something like this:

City(1)----->(*) City_Category_Relationship (*) <---------(1) Category

Logically, a city falls into many categories, and a category may be assigned to many cities. Or, there is a many-to-many relationship between city and category.

I am mapping this using what I call a 'double many to one', i.e. the 'link entity' is created by me, not hibernate, so that I can add properties to it other than just IDs (recommended at the end of the reference guide).

What I would like to do is come up with a query using the criteria API, that returns me a distinct list of all cites that fall into multiple categories. For example, show me the unique (distinct) list of cities that are 'Large', 'On the East Coast', and 'Cold in the Winter'.

Hibernate version : 2.1.7c

My mapping files look like this:

City
------------------------------------------
<hibernate-mapping>
<class
name="model.City"
table="City"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version">

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null" >
<generator class="native">
</generator>
</id>

<set
name="CityCategoryRelationship"
lazy="false"
inverse="false"
cascade="all-delete-orphan"
sort="unsorted"
order-by="display_order">

<key
column="city_id">
</key>

<one-to-many
class="model.CityCategoryRelationship"/>
</set>
...


CityCategoryRelationship
----------------------------------------------
<hibernate-mapping>
<class
name="model.CityCategoryRelationship"
table="city_category_relationship"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version">

<id name="id"
column="id"
type="java.lang.Long"
unsaved-value="null" >
<generator class="native">
</generator>
</id>

<many-to-one
name="cityRef"
class="model.City"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="city_id"
/>

<many-to-one
name="categoryRef"
class="model.Category"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
column="category_id"
/>
...


Category
-------------------------------

<class
name="model.Category"
table="category"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version">

<id
name="id"
column="id"
type="java.lang.Long"
unsaved-value="null" >
<generator class="native">
</generator>
</id>

<set
name="CityCategoryRelationship"
lazy="false"
inverse="false"
cascade="all-delete-orphan"
sort="unsorted"
order-by="display_order">

<key
column="category_id">
</key>

<one-to-many
class="model.CityCategoryRelationship"/>
</set>


<property
name="name"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="name"
/>
...


Thanks!

CP