Join Column Based Tables

Question

I am trying to combine three tables into one based on a column roles.ref_type. The column roles.ref_typehas values A(represents the table employee) and B(represents the table organisations).

roles

role_id     role_type    company     ref_id
   1            A           X           1
   2            B           Y           4
   3            A           Z           2
   4            B           X           5

employee (A)

employee_id    employee_name     joining_date
     1           Einstein         24/01/1998
     2           Maxwell          16/03/2002
     3           Graham           23/05/2006
     4           Boltz            06/02/2008

organisations (B)

org_id        org_name         org_max     org_location
  1           Stackoverflow      300           NY
  2           StackExchange       45           OR
  3           Facebook           300           NY
  4           Google              45           OR
  5           Yahoo              300           NY
  6           Rediff              45           OR

Expected Result:

role_id      role_type      company     ref_id    ref_name       ref_joining_date     ref_org_max
   1             A             X           1      Einstein          24/01/1998             NULL
   2             B             Y           4      Stackexchange        NULL                45
   3             A             Z           2      Maxwell           16/03/2002             NULL
   4             B             X           5      Yahoo                NULL                300

I tried the following query:

SELECT t1.role_id, t1.role_type, t1.company, t1.ref_id,
    CASE t1.role_type
        WHEN "A"
        THEN (
            SELECT 
                t2.employee_name as ref_name, 
                t2.joining_date as ref_joining_date,
                NULL as ref_org_max
            FROM
                employee t2
        )
        WHEN "B"
        THEN (
            SELECT 
                t3.org_name as ref_name, 
                NULL as ref_joining_date,
                t3.org_max as ref_org_max
            FROM
                organisations t3
        )
    END 
FROM roles t1

This does not work, because I know that the above request is not valid, but I am looking for something similar to get the result.

Answer 1

You need to use LEFT OUTER JOINinstead of the operatorCASE

try it

SELECT t1.role_id,
       t1.role_type,
       t1.company,
       t1.ref_id,
       COALESCE(e.employee_name, o.org_id) AS ref_name,
       e.joining_date                      AS ref_joining_date,
       o.org_max                           AS ref_org_max
FROM   roles t1
       LEFT JOIN employee
              ON e.employee_id = t1.ref_id
                 AND t1.role_type = 'A'
       LEFT JOIN organisations o
              ON o.org_id = t1.ref_id
                 AND t1.role_type = 'B' 

Answer 2

How about sth like this:

SELECT r.rolde_id, r.role_type, r.company, r.res_id,
       e.employee_name, e.joining_date,
       o.org_name, o.org_max, o.orx_location
FROM roles r
LEFT JOIN employee e
    ON (
        r.res_id = e.employee_id,
        r.role_type = 'A'
    )
LEFT JOIN organisations o
    ON (
        r.res_id = o.org_id,
        r.role_type = 'B'
    )

Answer 3

SELECT t1.role_id, t1.role_type, t1.company, t1.ref_id,
    CASE WHEN t1.role_type = "A"
        THEN (
            SELECT 
                t2.employee_name as ref_name, 
                t2.joining_date as ref_joining_date,
                NULL as ref_org_max
            FROM
                employee t2
        )
        ELSE (
            SELECT 
                t3.org_name as ref_name, 
                NULL as ref_joining_date,
                t3.org_max as ref_org_max
            FROM
                organisations t3
        )
    END 
FROM roles t1

Answer 4

- :

SELECT r.role_id, r.role_type, r.company, r.ref_id,
            e.employee_name as ref_name,
            e.joining_date as ref_joining_date,
            NULL as ref_org_max
        FROM roles r
        LEFT JOIN employee e
        ON (
            r.res_id = e.employee_id,
            r.role_type = 'A'
        )
 UNION ALL
SELECT r.role_id, r.role_type, r.company, r.ref_id,
       o.org_name as ref_name,
       NULL as ref_joining_date,
       o.org_max as ref_org_max
       FROM roles as r
       LEFT JOIN organisations o
       ON (
          r.res_id = o.org_id,
          r.role_type = 'B'
       )