Hi again, at first many thanks to both of you for your quick response! I hope that Antonis is not feeling offended but i agree with Thomas that the problem should be solved by means of the framework and not via SQL-statements written by hand.
Unfortunately, although spending the entire morning on this one, i couldn't come up with a solution yet. Thomas, you mentioned that using custom critera would be a good idea. This is what i was thinking too, but I don't rely get how the custom subquery and the parent query can be combined? So what i did is that i followed the subquery-way like this: Criteria criteria = new Criteria(); Criteria subCriteria = new Criteria(); subCriteria.addSelectColumn(AdressenreferenzPeer.KUNDENADRESS_ID); System.out.println(subCriteria.toString()); results = AdressenreferenzPeer.doSelect(subCriteria); criteria.addNotIn(AdressenreferenzPeer.KUNDENADRESS_ID, results); criteria.add(KundenadressePeer.KUNDEN_ID, kundeId, Criteria.EQUAL); criteria.setDistinct(); System.out.println(criteria.toString()); results = KundenadressePeer.doSelect(criteria); return results; 1st: In general: The doSelect of subquery throws an exception i don't really understand. Isn't it the right way to specify the columns you want in your resultset? com.workingdogs.village.DataSetException: Only 1 columns exist! => subCriteria.addSelectColumn(AdressenreferenzPeer.KUNDENADRESS_ID); similar to: SELECT kundenadress_id FROM adressenreferenz ?? 2nd: I don't think that this solution does not do the job cause its actually two queries instead of on cascaded one... 3rd: Thomas could you please give an example how you'd combine query and subquery according to this example? I really appreciate your help! Many thanks in advance, Jan -----Ursprüngliche Nachricht----- Von: Thomas Fischer [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 17. Oktober 2006 09:35 An: Apache Torque Users List Cc: Thomas Fischer Betreff: RE: Problems to set up a subquery Hm, writing SQL is one of the the things that Torque wants to avoid. One of the reasons for this is portability between databases. Though Torque is certainly not perfectly portable, it is much better than plain SQL. Another reason is that the compiler checks if the columns you use in your query is still in your database model (it will not find the relevant constants if a column is removed or renamed and the model is regenerated) (of cousre, the constants can also be used to build SQL to have the same effect, but this is quite a hassle). So in my eyes, one should think twice before using custom SQL. There are cases where it cannot be avoided (see below), but I would think twice before using it. Thomas On Tue, 17 Oct 2006, [EMAIL PROTECTED] wrote: > Hello, > > One thing that I have found very useful in complex queries is to > bypass the Criteria objects. > All you have to do is to write the SQL query, execute it using > BasePeer.executeQuery() to get a List of Records and then create a > list of persistent objects using the populateObjects of the corresponding > Peer class. > In this case, you can use: > > String query = "SELECT DISTINCT * FROM kundenadresse WHERE kundenadress_id > NOT IN (SELECT kundenadress_id FROM adressenreferenz)"; List > kundenadresses = > KundenadressePeer.populateObjects(BasePeer.executeQuery(query)); > > It works fine if you have a query of the form: "select * from > foo...", ie you select all the columns of a single table. I don't have > the source code handy to check if this query would work (my feeling is that > this would work also): > > String query = "SELECT * FROM FOO f, BAR b WHERE f.b = b.f AND ..."; > List list = FooPeer.populateObjects(BasePeer.executeQuery(query)); > > Enjoy, > Antonis > >> Hi, >> >> Subqueries are implemented and documented in the svn >> version(3.2.1-dev), but not in Torque 3.2.0. >> Either build Torque from svn or, if you want to use Torque 3.2.0. you >> can code the subquery as custom criteria (see >> http://db.apache.org/torque/releases/torque-3.2 >> /runtime/reference/read-from-db.html#Using_the_CUSTOM_modifier_to_use >> _custom_SQL) (you can also build the subquery via another Criteria >> and its toString()-method if you do not want to hand-code it). >> >> Thomas >> >> "Pfiester, Jan" <[EMAIL PROTECTED]> schrieb am >> 16.10.2006 >> 17:40:05: >> >>> Hi everyone, >>> >>> I ran into problems to setup the following query using criteria: >>> >>> SELECT DISTINCT * FROM kundenadresse WHERE kundenadress_id NOT IN( >>> SELECT kundenadress_id FROM adressenreferenz ); >>> >>> Could someone please give me some help to come up with a proper solution? >>> Hints for further reading on this topic would also be very much >> appreciated! >>> >>> Many thanks in advance, >>> Jan >>> >>> -------------------------------------------------------------------- >>> - 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] >> >> > > > -- > > > > --------------------------------------------------------------------- > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]