how to transfer sql to hsql !

javaite · **Posted:** Thu Mar 11, 2004 8:16 am

This is the sql.

Code:

select p0.id as id from _hm_person p0 where (p0.id not in(select p2.person from _hm_organization_person_link p2 , _hm_organization o1 where o1.id=p2.organization))

I have tried this.

Code:

select person from Person as person , Organization as org where person  not in elements(org.persons)

But I get unexpected results ...

Code:

select p0.id as id from _hm_person p0  , _hm_organization o1 where (p0.id not in(select p2.person from _hm_organization_person_link p2 where o1.id=p2.organization))

", _hm_organization o1" is moved to the front.

javaite · **Posted:** Fri Mar 12, 2004 2:27 am

emmanuel · **Posted:** Fri Mar 12, 2004 2:39 pm

This is expected. Use subselect for that I guess

javaite · **Posted:** Fri Mar 12, 2004 9:35 pm

Code:

select person from Person as person where person not in (select elements(org.persons) from Organization as org)

Thanks emmanuel! That solved this problem.