MySQL: Select from multiple tables with field to show which table the record came from

Question

I have 3 tables which contain very similar information. The tables are owners, managers, and employees. I am trying to pull information from all 3 tables, while still understanding which table each record came from. I also do not want duplicates. All tables have an email_id which relates to an email table which I am joining.

Also, it is very likely that the email_id will exist in multiple tables (i.e. An email could be an owner and manager, or all 3 even).

Here is what I have thus far:

SELECT email
FROM (
    (
        SELECT e.email
        FROM `owners` as o
        LEFT JOIN `email` as e
        ON e.email_id = o.email_id
        WHERE o.company_id = 3
        GROUP BY e.email
    )
    UNION ALL
    (
        SELECT e.email
        FROM `managers` as m
        LEFT JOIN `email` as e
        ON e.email_id = m.email_id
        WHERE m.company_id = 3
        GROUP BY e.email
    )
    UNION ALL
    (
        SELECT e.email
        FROM `employees` as emp
        LEFT JOIN `email` as e
        ON e.email_id = emp.email_id
        WHERE v.company_id = 3
        GROUP BY e.email
    )
) as `people`
ORDER BY email ASC

This query actually works perfectly. It removes duplicates from the tables individually, but keeps them across multiple tables. I still have no way to know which table each comes from. I tried to modify the query to this:

SELECT email, owner, manager, employee
FROM (
    (
        SELECT e.email, 'yes' as owner
        FROM `owners` as o
        LEFT JOIN `email` as e
        ON e.email_id = o.email_id
        WHERE o.company_id = 3
        GROUP BY e.email
    )
    UNION ALL
    (
        SELECT e.email, 'yes' as manager
        FROM `managers` as m
        LEFT JOIN `email` as e
        ON e.email_id = m.email_id
        WHERE m.company_id = 3
        GROUP BY e.email
    )
    UNION ALL
    (
        SELECT e.email, 'yes' as employee
        FROM `employees` as emp
        LEFT JOIN `email` as e
        ON e.email_id = emp.email_id
        WHERE v.company_id = 3
        GROUP BY e.email
    )
) as `people`
ORDER BY email ASC

This did not work because not all tables had the same columns. If I add all fields to all tables (i.e. SELECT e.email, 'yes' as owner, '' as manager, '' as employee) the query works, but all tables seem to have the same field. Not sure why.

Ultimately, I would like to have no duplicate email addresses in the returned record set, and have each record something like email = 'email address', owner = 'yes/no', manager = 'yes/no', employee = 'yes/no'.

Any ideas on how to accomplish this? If you need further information, just comment. Thanks!

score 1 · Accepted Answer · answered Oct 24 '13 at 16:09

1

Add a "table_name" column to each set of results to identify which table the results came from

(
    SELECT e.email, 'owners' as table_name
    FROM `owners` as o
    LEFT JOIN `email` as e
    ON e.email_id = o.email_id
    WHERE o.company_id = 3
    GROUP BY e.email
)
UNION ALL
(
    SELECT e.email, 'managers' as table_name
    FROM `managers` as m
    LEFT JOIN `email` as e
    ON e.email_id = m.email_id
    WHERE m.company_id = 3
    GROUP BY e.email
)
UNION ALL
(
    SELECT e.email, 'employees' as table_name
    FROM `employees` as emp
    LEFT JOIN `email` as e
    ON e.email_id = emp.email_id
    WHERE v.company_id = 3
    GROUP BY e.email
)

Alternatively add 3 columns to each block of results. i.e. add *is_owner, is_manager, is_employee* to all 3 select blocks

answered Oct 24 '13 at 16:09

vogomatix

4,856
2
23
46

Alternatively add 3 columns to each block of results. i.e. add *is_owner, is_manager, is_employee* to *all* 3 select blocks – vogomatix Oct 24 '13 at 16:11
And you can even remove the parentheses! – ypercubeᵀᴹ Oct 24 '13 at 16:30
I have actually tried to add 3 columns to each block of results and it is not working correctly. Can you show an example of this? – Chris Oct 24 '13 at 17:01
Actually, I got this to work. However, Is there anyway to group the results and maintain the columns? For example, currently 2 records have the same email address, one being is_owner and the other having is_manager. Any way to consolidate these and have both is_owner and is_manager there? – Chris Oct 24 '13 at 17:04

Mosty Mostacho · Answer 2 · 2013-10-24T16:12:05.263

You can do it by forcing a value in each row this way:

select t1.field1, t1.field2, 'YourFirstTable' as fromTable from t1
union all
select t2.field1, t2.field2, 'YourSecondTable' from t2

If you want to remove duplicates from the whole result set (where by "removing duplicates" I understand you want the email from any of the tables) you should wrap that in an outer query and apply a group by:

select field1, field2, max(fromTable)
    select t1.field1 as field1, t1.field2 as field2, 'YourFirstTable' as fromTable from t1
    union all
    select t2.field1, t2.field2, 'YourSecondTable' from t2
group by field1, field2

score 0 · Answer 3 · answered Oct 24 '13 at 16:12

        SELECT e.email,"owners" AS people
        FROM `owners` as o
        LEFT JOIN `email` as e
        ON e.email_id = o.email_id
        WHERE o.company_id = 3
        GROUP BY e.email
     UNION ALL
        SELECT e.email,"managers" AS people
        FROM `managers` as m
        LEFT JOIN `email` as e
        ON e.email_id = m.email_id
        WHERE m.company_id = 3
        GROUP BY e.email
    UNION ALL
        SELECT e.email,"employees" AS people
        FROM `employees` as emp
        LEFT JOIN `email` as e
        ON e.email_id = emp.email_id
        WHERE v.company_id = 3
        GROUP BY e.email;

MySQL: Select from multiple tables with field to show which table the record came from

3 Answers3