Jump to content


Photo

Better Way To Do This Simple Query?

sql join

  • Please log in to reply
4 replies to this topic

#1 CptnChainsaw

CptnChainsaw

    Member

  • Members
  • PipPip
  • 12 posts

Posted 05 December 2012 - 04:34 AM

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 :)

#2 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,667 posts
  • LocationBonita, FL

Posted 05 December 2012 - 04:58 AM

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.

Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#3 CptnChainsaw

CptnChainsaw

    Member

  • Members
  • PipPip
  • 12 posts

Posted 05 December 2012 - 05:18 AM

Hi,

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


Cheers :)

#4 CptnChainsaw

CptnChainsaw

    Member

  • Members
  • PipPip
  • 12 posts

Posted 06 December 2012 - 07:03 AM

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 :)

#5 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 06 December 2012 - 07:33 AM

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.
Keeping it simple.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com