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 firstname.lastname@example.org 2 Jane Doe email@example.com
Table 2 Structure
ID Name Value 1 work_email firstname.lastname@example.org 1 alt_work_email email@example.com 2 school_email firstname.lastname@example.org 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.
ID First Name Last Name Name Value 1 Mike Smith Email email@example.com 1 Mike Smith work_email firstname.lastname@example.org 1 Mike Smith alt_work_email email@example.com 2 Jane Doe Email firstname.lastname@example.org 2 Jane Doe school_email email@example.com
Any help on this would be amazing and very much appreciated!