Hi Thomas, I need to join the table in the first select with the one in the in-clause/subselect, i.e.
select * from book where book_id in (select max(b.book_id) from book b where b.title=book.title); I just dont know how I can archieve this, if it can be done w/out an alias, that's fine, it was just my first thought... thank you, Tino -----Urspr�ngliche Nachricht----- Von: Thomas Fischer [mailto:[EMAIL PROTECTED] Gesendet: Montag, 22. November 2004 11:54 An: Apache Torque Users List Betreff: Antwort: SQL->Criteria howto Hi Tino 1) I do not understand why you need the alias. Following sql works for me (on tutorial tables, on oracle) select * from book where book_id in (select max(book_id) from book); Thomas "Sperlich, Tino" <[EMAIL PROTECTED]> schrieb am 22.11.2004 11:40:18: > Hi all, > > I'd like to express this SQL in criteria syntax, but smth. is still missing: > > SELECT VORAVIS.VAVI_ID, VORAVIS.SEQUENZ > FROM VORAVIS > WHERE VORAVIS.AUFT_ID=67537 AND > VORAVIS.SEND_SEQUENZ > IN > ( > SELECT MAX(va.SEND_SEQUENZ) > FROM VORAVIS va > WHERE va.AUFT_ID=VORAVIS.AUFT_ID AND va.SEQUENZ=VORAVIS.SEQUENZ > ) > > My main problem is how to create the table alias "va" in the IN clause. > Using the criteria.addAlias() method gives me "FROM VORAVIS, VORAVISva", i.e. > double table statements. > For the in clause I use an adjusted criteria class supporting the > addIn(column, criteria) operation, basically just writing "column IN query". > > What am I missing? > > Thanks, > Tino > > --------------------------------------------------------------------- > 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]
