1

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!

  • 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. – danblack Dec 4 '18 at 4:55
  • 1
    You are learning why not to use EAV schema. – Rick James Dec 4 '18 at 5:24
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.