Thanks but I have one headache. The problem is pno isn't the primary key. The combination of pno and doctype field is the one. So the sql stmt only works if polno, doctype in doc <> polno,doctype in loan! Any ideas? ---------- From: Rob Page Sent: Wednesday, July 21, 1999 11:05 PM To: HCSCCS Cc: zope Subject: RE: [Zope] Z PyGresql DA questions MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.1960.3) Content-Type: text/plain
Hi. I'm building a loan system with Zap and I have a SQl statement problem. Let's say I have 2 table: loan and doc
Fields in doc: pno * doctype * docloc ...
Fields in loan: usrid * pno * doctype * ... Note: * means primary key
What I want to query is how do I get all the pno and its according type, that doesn't exist in the loan table? This is to make sure that the borrower does not select a document that had been loaned!!! In other words, all documents except those that are being borrowed.
How 'bout: SELECT * from doc WHERE pno NOT IN (SELECT pno FROM loan) --Rob