Join two tables

Question

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

I need to find all users who are developers or managers. What will sql look like?

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

Answer 1

One way to do this:

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  *
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 3

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)

Answer 4

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)

IN, EXISTS, . , , .

, , , .