Better Way To Do This Simple Query?

[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.