AW: Antwort: SQL->Criteria howto

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:Fischer@xxxxxxxxxxxxx]
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" <t.sperlich@xxxxxxxxxxxxxx> 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: torque-user-unsubscribe@xxxxxxxxxxxxx
> For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx
>


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@xxxxxxxxxxxxx
For additional commands, e-mail: torque-user-help@xxxxxxxxxxxxx