Thank Nathan a lot! We already figured out the NOT IN part. However your extra 'change other city' is also great! -Bruce
melinate wrote: > > 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] > > > -- View this message in context: http://www.nabble.com/work-with-excluding-set-in-Hibernate-MySQL-tf4889633s2369.html#a14179865 Sent from the AppFuse - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
