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

On Sat, 19 Sep 1998, Karl Denninger wrote:

> Date: Sat, 19 Sep 1998 17:55:14 -0500
> From: Karl Denninger <karl(at)denninger(dot)net>
> To: pgsql-sql(at)postgreSQL(dot)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(at)denninger(dot)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(at)palaver(dot)net
http://www.palaver.net