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.

SELECT Contact.Contact, 
       Managers.Manager 
FROM Contact 
LEFT OUTER JOIN Manager ON ???

should yield the following results:

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.

CodePudding user response：

To extract the (at most) two domain parts on the right, just do:

substring_index(substring_index(email,'@',-1),'.',-2)

Apply that to both Contact and Manager and join on the results being equal.