How do I create a list of emails from first name, last name and city?

user_14991033 0
Viewed 893

To clarify the question: I have a table with customers which includes the first_name and last_name and the address_id. Now I have to create a list of new emails which are made of the first name and last name and the city: firstname.lastname@city.com I am using the sakila database btw if that is of any help Could anybody explain to me, how I can accomplish this? Thanks in advance!

dbeaver mariadb sql database
2 Answers

The customer table has the first and last name, but not the city.
It has an address_id though, so we can join to the address table.

The address table doesn't have the city name either, but it has a city_id.
So we join the city table to the address table.

Now with the required parts available, the new email can be constructed.

select replace(
          lower(
             concat(cust.first_name, '.', cust.last_name, '@', city.city, '.com')
          ), ' ', '_') as new_email
from customer cust
join address addr
  on addr.address_id = cust.address_id
join city
  on city.city_id = addr.city_id
order by new_email;

Some cities have spaces, so they were replaced.

user_40034190

You can use mysql concat function to concatenate those columns into an email.

concat(first_name, '.', last_name, '@', city, '.com')

You also need to remove whitespaces from all involved column to generate valid emails using the replace function.

Knowing this, just join the three tables customer, address and city to get all the needed columns.

select first_name, last_name, 
concat(REPLACE(first_name, ' ', ''), '.', REPLACE(last_name, ' ', ''), '@', REPLACE(city, ' ', ''), '.com') as email
from customer c inner join address a on c.address_id = a.address_id
inner join city ct on a.city_id = ct.city_id

Fiddle

user_176450990
Related