Jay,
> There must be a standard SQL method to query multiple
> tables in the following way. Lets say we have two
> tables, 'allnames' and 'badnames'. We want to get the
> following result:
>
> "select name from allnames where name-is-not-in-badnames;"
>
> Clearly I'm an SQL novice but I haven't found any examples
> of such a query in my beginning SQL books.
Easy, two syntaxes:
SELECT allnames.name
WHERE allnames.name NOT IN (SELECT badnames.name
FROM badnames);
OR
SELECT allnames.name
WHERE NOT EXISTS (SELECT badnames.name
WHERE badnames.name = allnames.name);
The second is better for PostgreSQL versions 7.2 and 7.3. The first is better
for version 7.4.
--
-Josh Berkus
Aglio Database Solutions
San Francisco