Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199511070515.VAA26996@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199511070515.VAA26214@nobozo.CS.Berkeley.EDU>
To: Daryl Sayers <daryl@stone.oz.au>
cc: postgres@postgres.Berkeley.EDU
In-reply-to: daryl@stone.oz.au's message of Mon, 6 Nov 95 10:41:44 AEST
Date: Tue, 07 Nov 1995 00:15:17 -0500
From: Robert Patrick <Robert_Patrick@TIKA.NDIM.EDRC.CMU.EDU>
Resent-To: postgres-redist@postgres.Berkeley.EDU
Resent-Date: Mon, 06 Nov 95 21:15:54 -0800
Resent-XMts: smtp

> >O.K. call me lame, but I can't figure it out.....
> 
> >I have 2 classes, One for Company info and 1 for Contacts.
> >For every Contact record there is an id that points to the
> >Company it belongs to. My questions how can I retrieve all
> >the Companys that do not have a Contact record.
> 
> O.K. Here is some more information as the kind souls that answered
> me all suggested similar ideas that I had already tried.
> 
> retrieve (u.name)
> 	from u in company, v in contact 
> 	where u.id != v.id
> 
> This doesnt work because it goes through all the Companys and checks against
> all Contacts.

Right, this is the correct behavior for the query you specified (i.e.,
do the cartesian product of the two relations and filter out the cases
where u.id = v.id).

The problem is that Postgres does not support nested queries, which is
what would be require to do your search with a single query.  For
example, in other commercial SQL packages, you can say something like
(the exact syntax may or may not be correct, but you get the idea):

SELECT (company.name)
  FROM company
 WHERE company.id NOT IN (SELECT contract.id FROM contract)

To do this query with Postgres, you would need to "write some code"
(in C, perl, or any other language with a Postgres interface).

Robert

--
+------------------------------------------------------------------------+
| Robert Patrick (rp2y+@edrc.cmu.edu) Engineering Design Research Center |
| n-dim Group                                 Carnegie Mellon University |
| World Wide Web: http://tika.ndim.edrc.cmu.edu/~rp2y/Home.html          |
+------------------------------------------------------------------------+

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "postgres-request@postgres.Berkeley.EDU".
   If this fails, send mail to "post_questions@postgres.Berkeley.EDU" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================
              URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/