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 m[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!