Hi Joe, There is not a lot, but referring the outer query in an inner query is something you can't do with Empire-db.
However I would not recommend to use subqueries in the where clause anyway. I'd rather solve this with an outer join similar to this: select t1.id from table1 t1 left join ( select t2.id from table2 t2 ) q1 on q1.id = t1.id where q1.id is null This is something you can to by wrapping your DBCommand in a DBQuery (see advanced samples for an example). Would that be a suitable solution for your problem? Regards Rainer where not exists > Joe Thurbon wrote: > re: Select where not exists... > > 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 > [email protected] | www.intersect.org.au > T: +61 2 8079 2535 | M: +61 413 609 094 > Level 12, 309 Kent St, Sydney NSW 2000, Australia
