On Apr 6, 2009, at 8:11 PM, David Gardner wrote:
>
> I am trying to get all of the rows in table A that do not have a match
> in table B. I believe the problem is that I am using a text foreign
> key,
> and for the rows I am looking for the field will still have a value,
> it
> just won't match anything in table B.
>
> To make things confusing the table 'qstatus' is mapped to a class
> called
> 'JobInfo', also the qstatus table is a temporary table populated with
> values of what is currently running, so there are no foreign keys or
> indexes on the table, the qstat_table.c.job_number field is unique but
> isn't a primary key as far as PostgreSQL is concerned.
>
> This is what I am trying to do:
>
> SELECT farm.qstatus.* FROM farm.qstatus
> LEFT OUTER JOIN farm.job ON qstatus.job_name=job.name
> WHERE job IS NULL
> ORDER BY qstatus.job_name;
this is usually easiest via NOT EXISTS
select * from table where not exists (select 1 from othertable where
othertable.foo=table.bar)
> mapper(JobInfo, qstat_table, primary_key=[qstat_table.c.job_number],
> properties={
> 'Chunk':relation(Chunk, lazy=True, uselist=False),
> 'Job' : relation(Job,
> primaryjoin=(qstat_table.c.job_name==job_table.c.name),
> foreign_keys=[qstat_table.c.job_name], lazy=True, viewonly=True,
> uselist=False)
> }, save_on_init=False)
so a query like this would do it:
session.query(JobInfo).filter(~JobInfo.Job.has())
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
