Select where not exists...

[Joe Thurbon]

First, thanks for a terrific library. It's really nice!

I have a problem, though. Here's a simplified version.

Consider two tables:
 table1 with a single column 'id'
 table2 with a single column 'id'

I'd like to find all of the entries in table one that are not in table two.

After a lot of profiling with various databases, the query I want to generate is

select t1.id 
from table1 t1 
where not exists
(
   select t2.id 
   from table2 t2
   where t1.id = t2.id
)

Note that the inner query refers to the outer table t1 on the second last line.

I thought that the empire-db construction would be something like:

Table1 table1 = ...
Table2 table2 = ...

DBCommand subSelect  = db.createCommand();
subSelect.select(table2.id);
subSelect.where(table1.id.is(table2.id));

DBCommand topSelect  = db.createCommand();
topSelect.select(table1.id);
topSelect.where(new DBExistsExpr(subSelect).not());

However, this generates the query 

select t1.id 
from table1 t1 
where not exists
(
   select t2.id 
   from table2 t2, t1
   where t1.id = t2.id
)

The difference is the second last line, where t1 is added to the from list of 
the sub-select. Which basically means that the 'not-exists' evaluates to 
'false'. 

Any hints or clues?

Cheers,
Joe


--
Dr Joe Thurbon | eResearch Analyst | Intersect
joe.thur...@intersect.org.au | www.intersect.org.au
T: +61 2 8079 2535 | M: +61 413 609 094 
Level 12, 309 Kent St, Sydney NSW 2000, Australia