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!