Thread

1. To create the query for a select where one is NOT in the other

   Karl Denninger <karl@denninger.net> — 1998-09-19T22:55:14Z

   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
   
   
   

2. Re: [SQL] To create the query for a select where one is NOT in the other

   Eric J McKeown <ericm@palaver.net> — 1998-09-20T23:23:05Z

   On Sat, 19 Sep 1998, Karl Denninger wrote:
   
   > Date: Sat, 19 Sep 1998 17:55:14 -0500
   > From: Karl Denninger <karl@denninger.net>
   > To: pgsql-sql@postgreSQL.org
   > Subject: [SQL] To create the query for a select where one is NOT in the other 
   > 
   > 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;
   
   I think what you need to do is use a subquery:
   
   select key_field from table1 where key_field NOT IN (select key_field from
   table2) ;
   
   The subquery should select a list of all the keys in table 2, and then no
   key from talbe 1 that matches anything in this list will be included in
   your search results.
   
   Make sense??
   
   eric
   
   > 
   > 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
   > 
   > 
   > 
   
   _______________________
   Eric McKeown
   ericm@palaver.net
   http://www.palaver.net