Change query to replace string by values in table of replacement

To store emails list I have two quite simple tables, domain and mailbox, so **user@domain.com** is stored as: **domain** | id | domain_name | |----|-------------| | 1 | domain.com | **mailbox** | id | local_part | domain_id | |----|------------|-----------| | 1 | user | 1 | (*local_part* is email part to the left of '@' sign, and *domain_id* refers to id value of domain in **domain** table). Until this point, all is simple, and I can **list all boxes** like this:

SELECT CONCAT(m.local_part, '@', d.domain_name) 
FROM domain d, mailbox m 
WHERE m.domain_id=d.id

Now I need to add some way to support domain alias (so e.g. domain.net become alias of domain.com). So I create table domain_alias: | domain_old | domain_new | |-------------|------------| | domain.com | domain.net | Now I try to modify above query so all boxes in domain_old be shown as domain_new boxes (e.g. instead of user@domain.com this query yields user@domain.net). I suspect this might be not that hard but I must be missed something so please advice! P.S. I do that with MariaDB 10.3.