joining two tables

Question

I have users table. There are three other tables: developers, managers, testers. All of these tables have a foreign key user_id.

I need to find all the users who are either developer or manager. What the sql will look like?

Update: Someone can be both a developer and a manager.

Answer 1

One way to do it would be

SELECT  u.*, 'Developer'
FROM    users u
        INNER JOIN developer d ON d.user_id = u.user_id
UNION ALL 
SELECT  u.*, 'Manager'
FROM    users u
        INNER JOIN manager m ON m.user_id = u.user_id

Answer 2

SELECT
    user_id
     /* ...other desired columns from the user table... */
FROM
    user
WHERE
    user_id IN (SELECT user_id FROM developer UNION SELECT user_id FROM manager)

Here I am using IN rather than EXISTS so that the developer and manager tables only need to be queried one time. It's possible that the optimizer may do this anyway, but this makes it explicit.

Also, this solution does not return duplicates for users who are both managers and developers.

Answer 3

SELECT  *
FROM    users u
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    developers
        WHERE   user_id = u.id
        UNION ALL
        SELECT  NULL
        FROM    managers
        WHERE   user_id = u.id
        )

Answer 4

SELECT    u.*, 
          CASE d.user_id IS NULL THEN 'N' ELSE 'Y' END is_developer,
          CASE m.user_id IS NULL THEN 'N' ELSE 'Y' END is_manager
FROM      users u                -- all users
LEFT JOIN developers d           -- perhaps a developer
ON        u.user_id = d.user_id
LEFT JOIN manager m              -- perhaps a manager
ON        u.user_id = m.user_id
WHERE     d.user_id IS NOT NULL  -- either a developer
   OR     m.user_id IS NOT NULL  -- or a manager (or both)