Mike Zornek <[EMAIL PROTECTED]> wrote on 01/20/2005 11:01:38 AM:
> I have the following query which will get me all of the emails for my
> current membership:
>
> SELECT email.email_address
> FROM member, email
> WHERE
> member.member_primary_email_id = email.email_id
> AND member.member_standing != "Dropped"
> ORDER BY email.email_address
>
> I also have a query which will get me the email addresses of anyone who
has
> updated their profile (and thus has a row in updatehistory)
>
> SELECT DISTINCT email.email_address
> FROM member, email, updatehistory
> WHERE
> member.member_primary_email_id = email.email_id
> AND member.member_standing != "Dropped"
> AND member.member_id = updatehistory.member_id_editor
> ORDER BY email.email_address;
>
> How would I get the emails for every member who does NOT have a row in
> updatehistory?
>
> ~ Mike
> -----
> Mike Zornek
> Web Designer, Media Developer, Programmer and Geek
> Personal site: <http://MikeZornek.com>
>
First, I need to you recognize that listing tables with commas in the FROM
clause creates an implicit INNER JOIN between the tables. That means that
your query
SELECT DISTINCT email.email_address
FROM member, email, updatehistory
WHERE member.member_primary_email_id = email.email_id
AND member.member_standing != "Dropped"
AND member.member_id = updatehistory.member_id_editor
ORDER BY email.email_address;
IS EQUIVALENT to this more explicitly defined query:
SELECT DISTINCT email.email_address
FROM member
INNER JOIN email
ON member.member_primary_email_id = email.email_id
INNER JOIN updatehistory
ON member.member_id = updatehistory.member_id_editor
WHERE member.member_standing != "Dropped"
ORDER BY email.email_address;
In order to detect non-matches between two tables, you perform an outer
join between them and look for the records that don't match. They will be
easy to spot because the engine will put NULLs into every column of the
"optional" table for those rows that don't match up with the "required"
table. In a LEFT JOIN, the table on the left of the clause is the required
table and the one on the right is the optional table. Reverse that for
RIGHT JOINs.
With that knowledge in hand, we will now change the query to detect which
rows of member (required) do not match any rows in updatehistory
(optional). We change the JOIN on that table from INNER JOIN to LEFT JOIN
and look for NULL values where there shouldn't be any by adding another
condition to the WHERE clause (they should only exist if rows between the
two tables didn't meet your ON conditions(s), correct?). We don't need to
change anything else.
SELECT DISTINCT email.email_address
FROM member
INNER JOIN email
ON member.member_primary_email_id = email.email_id
LEFT JOIN updatehistory
ON member.member_id = updatehistory.member_id_editor
WHERE member.member_standing != "Dropped"
AND updatehistory.member_id_editor IS NULL
ORDER BY email.email_address;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
