[Zope] Z PyGresql DA questions

[Paul Chung Chee Soong]

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