ID    First Name    Last Name    Email
1     Mike          Smith        mikesmith@test.com
2     Jane          Doe          jane@doe.com

ID    Name             Value
1     work_email       mikesmith@company.email
1     alt_work_email   mikesmith@second.email
2     school_email     jane@school.edu
2     address 1        123 west main street
2     city             Houston

SELECT
tb1.id,
tb1.first_name,
tb1.last_name,
tb1.email as Value,
tb2.value as Value2,
FROM  table1 as tb1
LEFT JOIN table2 as tb2 ON tb2.name LIKE '%email%' AND tb2.value LIKE '%@%'
AND tb2.value != '' AND tb2.client_id = tb1.id

ID    First Name    Last Name    Name           Value
1     Mike          Smith        Email          mikesmith@test.com
1     Mike          Smith        work_email     mikesmith@company.email
1     Mike          Smith        alt_work_email mikesmith@second.email
2     Jane          Doe          Email          jane@doe.com
2     Jane          Doe          school_email   jane@school.edu