On May 28, 2008, at 8:24 PM, EricHolmberg wrote:
> > CREATE TABLE alg (id INT NOT NULL, name VARCHAR(32)); > CREATE TABLE email (id INT NOT NULL, subject VARCHAR(256)); > CREATE TABLE thread (id INT NOT NULL, algid INT, emailid INT); > > > This is great, I get my list of emails that don't have any threading > records . . . but now I need to know how many emails don't have > threading records for a particular algorithm. . . Do I have to do this > using a temporary table? to find members that "don't" have something related, the NOT EXISTS approach is clearest: select * from email where not exists (select 1 from thread where emailid=email.id and algid=<some algorithm id>) In SQLA, assuming you have an association mapping of Email->Thread- >Alg, the any() function is useful for generating EXISTS clauses easily: some_alg = sess.query(Alg).get(8) sess.query(Email).filter(~Email.threads.any(Thread.alg==some_alg)) The clause "Thread.alg=<some alg>" *should* just generate the clause "thread.algid=8"...it shouldn't pull in the "alg" table at all since its not needed for many-to-one comparison. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---