MYSQL Select data from two different tables BUT represent the data in multiple rows

Question

I am needing to select all email addresses from two different tables with a single query. The goal of this is to list out all email addresses a client has per row.

Both tables are structured differently from one another.

Table 1 Structure

ID First Name Last Name Email
1 Mike Smith [email protected]
2 Jane Doe [email protected]

Table 2 Structure

ID Name Value
1 work_email [email protected]
1 alt_work_email [email protected]
2 school_email [email protected]
2 address 1 123 west main street
2 city Houston

Normally, If I knew the the exact Name for Table 2 would do a LEFT JOIN and be golden but this is not the case.

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 

I know the above query is inaccurate but I needing something like this to collect all email addresses from Table 1 and where Name contains the word email from Table 2 and display those emails along with the Name value associated with the email address. Table 1 Name value will be the column header of Email. Desired output of what is needed is above.

Desired Output

ID First Name Last Name Name Value
1 Mike Smith Email [email protected]
1 Mike Smith work_email [email protected]
1 Mike Smith alt_work_email [email protected]
2 Jane Doe Email [email protected]
2 Jane Doe school_email [email protected]

Any help on this would be amazing and very much appreciated!

• danblack: You want a match between tables? and its not the id? And you haven't specified it? Not sure why your almost right query isn't all right.
• Rick James: You are learning why not to use EAV schema.

Answer 1

 SELECT id, First_Name, Last_Name, 'Email' Name, Email Value
 FROM table1
UNION ALL /* or UNION DISTINCT */
 SELECT tb1.id, tb1.First_Name, tb1.Last_Name, tb2.Name, tb2.Value, 
 FROM table1 tb1
 LEFT JOIN table2 tb2 
 ON tb2.Name LIKE '%email%' 
 AND tb2.Value LIKE '%@%' 
 AND tb2.client_id = tb1.id 
/* ORDER BY 1 */

PS. AND tb2.value != '' condition is excess. If false, then AND tb2.Value LIKE '%@%' is false too.