I have the following classes : PolicyDim, ClaimantSnapshotFact and DepartmentDim.

PolicyDim - ClaimantSnapshotFact = 1-M
ClaimanSnapshotFact - DepartmentDim - 1-1

I want all policies with a policy number of '123456' that have an effective date between x and y and belong to department name 'No Department'

Here is the data in the database :

- There are 3 policies with policy number '123456' with different effective dates dt1, dt2 and dt3
- Only 1 policy is tied to ClaimantSnapshotFacts (the others have no ClaimantSnapshotFacts)
- This 1 policy has 2 CliamantSnapshotFacts, and both these ClaimantSnapshotFacts have a department name of 'No Department'

So if I want all policies that have a policy number of '123456' that have effective dates between dt1 and dt3 and belong to a department called 'No Department', here is my HQL :

select policy
from com.accidentfund.common.db.hibernate.PolicyDim as policy
join policy.claimantSnapshotFacts as facts
where policy.policyNumber = ?
and policy.effectiveDate between ? and ?
and facts.departmentDim.departmentName = ?

I am supplying the right params to the ?

But I get back 2 policy (PolicyDim) objects, both of which are the same object. Shouldnt this query above return me just 1 ?

Should I be supplying the 'distinct' keyword here ? I dont think I need to, but if so, please explain why.

Unfortunately, hibernate does not print the SQL for me on the console despite my show_sql=true, so I cant see the query that is being run.

Please let me know why I get back duplicate copies of the same PolicyDim object.

This is exactly why a join in SQL will produce 2 identical rows. You should really try getting the SQL logging to work. (there is a log4j logger too for sql logging). For your problem, use distinct.