I have two tables with email addresses. Some emails have sub-domains, and others don't. I need to LEFT OUTER JOIN these tables based on the domain name and extension, while ignoring any sub-domains and anything left of the @ symbol.
| Contacts.Contact | Managers.Manager |
|---|---|
| bob@api.ibm.com | sarah@ibm.com |
| jim@sales.ibm.com | jane@mgt.yahoo.ca |
| joe@ibm.com | fred@google.com |
| zoe@yahoo.com | sam@facebook.co.uk |
| elf@api.yahoo.ca | |
| frank@yahoo.ca | |
| jack@oracle.com |
SELECT Contact.Contact,
Managers.Manager
FROM Contact
LEFT OUTER JOIN Manager ON ???
should yield the following results:
| Field: Contact | Field: Manager |
|---|---|
| bob@api.ibm.com | sarah@ibm.com |
| jim@sales.ibm.com | sarah@ibm.com |
| joe@ibm.com | sarah@ibm.com |
| zoe@yahoo.com | |
| elf@api.yahoo.ca | jane@mgt.yahoo.ca |
| frank@yahoo.ca | jane@mgt.yahoo.ca |
| jack@oracle.com |
The ibm.com and yahoo.ca emails were matched. So basically, the pattern is: IGNORE@IF_THIS_EXISTS_THEN_IGNORE.MATCH.MATCH Is this possible? If it is, then what is the correct SELECT statement to yield these results? Thanks.