[thelist] SQL: Negative Join?
Joshua Olson
joshua at waetech.com
Sun Sep 28 15:53:32 CDT 2003
----- Original Message -----
From: "Frank" <frank at frankmarion.com>
Sent: Sunday, September 28, 2003 4:16 PM
> I've got two tables each with client names and phone numbers. One table is
> a new list, the other is a list of clients to delete. Is there a way of
> doing a "negative join"? Something like
>
> SELECT k.columns
> FROM keepers k
> LEFT JOIN tossers t
> ON k.client <> t.client;
Frank,
Based on your explanation I'm not certain what it is you want to do. But
here's a guess:
Return data for all clients who do not have a matching table in the tossers
table.
There are two ways to do this:
1. Brute force. Use a subselect to make sure the record doesn't exist:
SELECT k.columns
FROM keepers k
WHERE k.client NOT IN (SELECT client FROM tossers)
2. A little finesse. Use an OUTER JOIN:
SELECT k.columns
FROM keepers k
LEFT OUTER JOIN tossers t
ON k.client = t.client
WHERE t.client IS NULL
<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168
More information about the thelist
mailing list