Assuming I understand you correctly, this is pretty trivial. Did you read the Peers howto and the Criteria howto?
Criteria criteria = new Criteria(); //do the joins criteria.addJoin(UserPeer.USER_ID, UserGroupPeer.USER_ID); criteria.addJoin(UserGroupPeer.GROUP_ID, GroupPeer.GROUP_ID); //limit to group A criteria.and(GroupPeer.GROUP_NAME, "A"); //get a list of users satisfying the above criteria Vector results = UserPeer.doSelect(criteria); It is often helpful to see the SQL log that Torque generates for you. See Torque.properties to see where this log is. HTH, Ryan -----Original Message----- From: Derek Stevenson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 24, 2002 6:02 PM To: Turbine Users List; [EMAIL PROTECTED] Subject: help on inner join / subselect using Criteria objects 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]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
