Received: from postal.grin.net (root@postal.grin.net [209.104.220.39]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA73506 for ; Mon, 12 Apr 1999 15:12:07 -0400 (EDT) (envelope-from walt@stimpy.com) Received: from stimpy.com (alice.stimpy.com [208.211.184.17]) by postal.grin.net (8.8.5/8.8.5) with ESMTP id MAA15632; Mon, 12 Apr 1999 12:11:58 -0700 (PDT) Message-ID: <3712426C.8342F216@stimpy.com> Date: Mon, 12 Apr 1999 11:58:52 -0700 From: Walt Bigelow X-Mailer: Mozilla 4.07 [en] (Win95; I) MIME-Version: 1.0 To: pgsql-sql@postgresql.org, walt@stimpy.com Subject: subqueries Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I am having difficulites geting the following query to run properly: I have 1 table that is called tbladdress that keeps company address information AND the company's contacts (employees) in the same table. The company has a type id of 1, and the contact has a type id of 2. The contact aslo has a field filled in which points to the agencyid field of the parent company. Not every company has a contact. The query I was trying to get working: SELECT agencyid, first, last FROM tbladdress WHERE type = 1 AND agencyid NOT IN (SELECT distinct agencylink FROM tbladdress WHERE type = 2) ORDER BY last; This query returns 0 rows. I manually walked through the data and found, yes, there are company records that should have been returned (about 150 of them). Just to test I did the following: CREATE table aa (name varchar(50), number int4); CREATE table bb (name varchar(50), number int4); I populated those tables with: aa: Mildred | 5 Sam | 6 bb: Paul | 1 Sativa | 2 and the select: SELECT * FROM aa WHERE number not in (select number from bb); and I get the desired response: name |number -------+------ Mildred| 5 Sam | 6 What am I doing wrong? Can the SQL query not do a nested select on a table it is currently doing a select on? Thanks for any insight! Walt walt@stimpy.com