4 replies to this topic

[CptnChainsaw]

Hi,

Can anyone suggest a better way to write this query? I only want to find email addresses in table "a" if they are not in table "b".

SELECT a.email
FROM a, b
WHERE a.email != b.email
LIMIT 0 , 30

Cheers in advance.

CptnChainsaw

[kicken]

SELECT
   a.email
FROM a
LEFT JOIN b ON b.email=a.email
WHERE
  b.email IS NULL

That will join to table b matching up rows by the email field.  If no row exists in table b matching table a's email value, then all the fields in b will be NULL for that record.  The WHERE condition then filters the result to only the rows where the b.email field is null, meaning you only get the email's from a with no match in b.

[CptnChainsaw]

Hi,

Thank you for that, that's exactly what I was looking for.


Cheers

[CptnChainsaw]

Hi,

If I wanted to join on another table would the query be something like this:

SELECT
   a.email
FROM a
LEFT JOIN b ON b.email=a.email
LEFT JOIN c ON c.email=a.email
WHERE
  b.email IS NULL AND c.email IS NULL

I tried this and its not quite right. I would only want to return records from table a if they are not in b and c.

Any further suggestions would be appreciated as always.

Thank you

[Christian F.]

According to the WHERE clause, you've specifically asked for e-mails that were not in both table B and table C. Meaning if the e-mail is in either of those tables, it would be skipped. If you want e-mails that are missing from only one of those table, replace the AND with an OR.