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

eavjoin;MySQL

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        mikesmith@test.com
2     Jane          Doe          jane@doe.com

Table 2 Structure

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

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          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

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

Best Answer

    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.

Related Question