Eldelshell Eldelshell - 13 days ago 13
SQL Question

Find emails that are present on a spammers table of domains

I have a table with user emails (Accounts) and another with a list of temporary email addresses domains (Spammers).

Accounts table



id | email
----+-------------------------
1 | test111122@example.com
2 | test1111223@example.com
3 | test@sharklasers.com


Spammers table



id | domain
-----+-----------------------------------------
1 | 0-mail.com
2 | 0815.ru
3 | sharklasers.com


Results after query



id | email
----+-------------------------
3 | test@sharklasers.com


How can I obtain all the emails on the Accounts table that are from Spammers?

I know there are many accepted email formats, but currently I'm only interested on the "standard" ones.

Thanks!

Answer

You can use a simple JOIN with a LIKE search:

select email from users u
join spammers s 
  on u.email like '%' || s.domain;

SQL Fiddle