This is the SQL statement that I have.

SELECT FIRSTNAME, LASTNAME, USERTYPE
FROM USERPROFILE
INNER JOIN USERLOGINSTATUS
ON USERPROFILE.USERID=USERLOGINSTATUS.USERID
ORDER BY USERPROFILE.FIRSTNAME

And I'm trying to execute the code below that I thought the equivalent to hibernate DetachedCriteria and expected to only have two data as a result.

DetachedCriteria dc = getDetachedCriteria();
DetachedCriteria userLoginCriteria = DetachedCriteria.forClass(UserLoginStatus.class);
userLoginCriteria.setProjection(Projections.distinct(Projections.property("userId")));
dc.add(Subqueries.propertyIn(UserField.id.name(), userLoginCriteria));

DetachedCriteria profileCriteria = dc.createCriteria("profile");
profileCriteria.addOrder(Order.asc("firstName"));
return getAll(dc, pageSetting);

But unfortunately this is the unexpected result: I am having a multiple data result.

Name | Type |

1. Ben Jones | User | 2. Ben Jones | User | 3. Tom Homer | Guest | 4. Tom Homer | Guest |

Is anyone there knows the exact equivalent DetachedCriteria or a solution for this?

Many Thanks,

xtian