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

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

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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