Don't know if you already found a solution, but here is what the SQL would look like... I imagine the HQL would be similar..
Here is how to get just the "other" users: SELECT user.* FROM user WHERE user.city NOT IN (SELECT city.name FROM city WHERE state = 'CA') Here is how to get all users and change the city name to "other" if not in city table [don't forget to add the other user properties to the select]: SELECT user.username, CASE WHEN (city.name IS NULL) THEN 'Other' ELSE city.name END as city FROM user LEFT OUTER JOIN city on (user.city = city.name) Nathan ----- "BruceLee" <[EMAIL PROTECTED]> wrote: > Dear Team, > > This question may be best in Hibernate forum. However I give it try > here > since many are knowledgeable and helpful in this forum. I use appfuse > 1.9.4 > with WW2. My problem is, > > There is City field in user table, some users are in big cities like > LA, San > Francisco and others in not well-known cities. I also have a city > table > containing records of most big cities with its state. > On JSP page, I want list user by city: LA, San Francisco, Other (all > other > cities in CA). > > How can I create a map/set in Hibernate? > 1) Get set1 of city list in the state from city table > 2) Get all users whose city is *not* in above set1 > > I think Hibernate or MySQL should have a way, but I don't know. Maybe > one > way is to ask:) > Thanks a lot in advance! > -Bruce > -- > View this message in context: > http://www.nabble.com/work-with-excluding-set-in-Hibernate-MySQL-tf4889633s2369.html#a13996042 > Sent from the AppFuse - User mailing list archive at Nabble.com. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
