I don't quite know how to explain it right, but I guess I want to create a subquery that references the entity of the main query.
Here is the SQL that does exactly what I want: SELECT * FROM job JOIN datacontainer ON datacontainer.id = job.data_container_id WHERE NOT (EXISTS (SELECT 1 FROM job AS other_job WHERE other_job.data_container_id = datacontainer.id AND other_job.id < job.id)); The key is that in 'other_job.id < job.id' job is the job from the main query. And here is my best attempt at doing the same thing in sqlalchemy: Job_A = aliased(Job) DBSession.query(Job_A).join(DataContainer).filter(~ DataContainer.jobs.any(Job.id < Job_A.id)).all() This, however, produces 'FROM job, job AS job_1' in the subquery, which introduces another job that is not the same as the one in the main query. Could someone please help me get this right? Thanks, Gunnar -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/3HwPA-D2Lf4J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.