I am trying to use the Criteria object to query a database and return a distinct list of users which are in the same group(s) as a given user. Here are my tables:
USER ---- USER_ID FIRST_NAME LAST_NAME USER_GROUP ---------- USER_ID GROUP_ID GROUP ----- GROUP_ID GROUP_NAME USER_GROUP is a join table, in which a user can be part of one or more groups. Say for example user A is in group 1 and 2, user B is in group 1, user C is in group 2, and user D is in groups 1 and 2. I want to use the Criteria object to return a Vector of User objects a la UserPeer.doSelect(criteria). Based on this sample data, I want to find all users which belong to user A's groups. My result should contain users A, B, C, and D, all occurring only once in my Vector. As a first pass, I put together a SQL query that would do the job. The following will seemingly work fine to provide the desired list: select distinct c.* from USER_GROUP as a, USER_GROUP as b, USER as c where a.USER_ID='A' and a.group_id=b.group_id and b.user_id = c.user_id; I'm having trouble translating this into something Criteria can use, however. I first thought I might need to use an inner join or subselect, although since we're using MySQL I don't think I can use subselects/inner joins (maybe Criteria translates it into something mysql will understand?). The query above does work against mysql, however. Do I need to use Criteria.addAlias to do table aliasing as I've done in my query? If so, what is the proper syntax? Alternatively, I thought I might have to use BasePeer.executeQuery() instead and pass it the above SQL query directly. Any help would be greatly appreciated. Derek -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
