eeetee eeetee -4 years ago 89
MySQL Question

Combining 3 separate columns into one for it to be used in an IN clause

This is purely an efficiency question. I have two tables:

One where user IDs are stored based on their position within an entity

id | supervisor | manager | worker
-------+----------------+--------------+---------------
1 | 12 | 15 | 32
2 | 12 | 42 | 22


And the one with the user info

id | name | email
-------+----------------+---------------------
12 | Bob | bob@example.com
15 | Dave | dave@example.com


Is it possible to do this with only one
IN
clause instead of 3?

SELECT `name`, `email`
FROM `tbl_users`
WHERE `id` IN
(SELECT `supervisor` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID')
OR `id` IN
(SELECT `manager` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID')
OR `id` IN
(SELECT `worker` FROM `tbl_warehouse` WHERE `id` = 'WAREHOUSEID')


I tried a few things trying to combine the
supervisor
,
manager
,
worker
columns into one column so the
IN
clause works but did so without success

Cheers

Answer Source

You can use EXISTS and use the IN inside the subquery:

select `name`,
    `email`
from `tbl_users` u
where exists (
        select 1
        from `tbl_warehouse` w
        where w.id = 'WAREHOUSEID'
            and u.id in (w.supervisor, w.manager, w.worker)
        )

You achieve this using JOIN too:

select u.name, u.email
from tbl_users u
join tbl_warehouse w on u.id in (w.supervisor, w.manager, w.worker)
where w.id = 'WAREHOUSEID'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download