Received: from Kitten.mcs.com (Kitten.mcs.com [192.160.127.90]) by hub.org (8.8.8/8.8.8) with ESMTP id SAA15767 for ; Sat, 19 Sep 1998 18:55:15 -0400 (EDT) (envelope-from karl@Mars.mcs.net) Received: from Mars.mcs.net (karl@Mars.mcs.net [192.160.127.85]) by Kitten.mcs.com (8.8.7/8.8.2) with ESMTP id RAA18877 for ; Sat, 19 Sep 1998 17:55:14 -0500 (CDT) Received: (from karl@localhost) by Mars.mcs.net (8.8.7/8.8.2) id RAA01872 for pgsql-sql@postgresql.org; Sat, 19 Sep 1998 17:55:14 -0500 (CDT) Message-ID: <19980919175514.A1837@mcs.net> Date: Sat, 19 Sep 1998 17:55:14 -0500 From: Karl Denninger To: pgsql-sql@postgresql.org Subject: To create the query for a select where one is NOT in the other Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.93.2i Organization: Karl's Sushi and Packet Smashers I want to specify the following SELECT and can't figure it out: Table "a" and table "b" have identical schemas I want to select all the records in "a" in which a key field in "a" does NOT have a corresponding row in "b". I know how to do this if I want the opposite, that is, if I want all the rows in "a" in which the key IS present in "b" this works: select a.field1, a.field2 from t1 a, t2 b where a.field1 = b.field1; However, the intuitive modification of this (change "=" to "<>") produces a monster result in which each row in "a" other than the one which matches gets output for each instance of "b" (which if you think about it does make sense). How do I structure an SQL statement to get the desired rows returned? -- -- Karl Denninger (karl@denninger.net) "Yes, the president should resign. He has lied to the American people, time and time again, and betrayed their trust. Since he has admitted guilt, there is no reason to put the American people through an impeachment. He will serve absolutely no purpose in finishing out his term, the only possible solution is for the president to save some dignity and resign." ________________________________________________________________________ 12th Congressional District Hopeful William Jefferson Clinton, during the Nixon investigations