agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Robert Patrick <Robert_Patrick@TIKA.NDIM.EDRC.CMU.EDU>
To: Daryl Sayers <daryl@stone.oz.au>
Cc: postgres@postgres.Berkeley.EDU
Date: Tue, 07 Nov 1995 00:15:17 -0500
Message-ID: <199511070515.VAA26214@nobozo.CS.Berkeley.EDU> (raw)

> >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/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: postgres@postgres.berkeley.edu
  Cc: Robert_Patrick@TIKA.NDIM.EDRC.CMU.EDU, daryl@stone.oz.au
  Subject: Re: 
  In-Reply-To: <199511070515.VAA26214@nobozo.CS.Berkeley.EDU>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox