CODEWITHSUNDEEP
mysqlsql
Mr_Green
Mr_Green

Reputation: 41832

Merge tables based on a column

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

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 isn't working because I know the above query is invalid but I am looking for something similar query to get the result.

Upvotes: 4

Views: 74

Answers (4)

Mariusz Szurgot
Mariusz Szurgot

Reputation: 432

Try something like this:

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'
       )

Upvotes: 0

brclz
brclz

Reputation: 826

What about sth similar to 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'
    )

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

You need to use LEFT OUTER JOIN instead of CASE statement

Try this 

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'

Upvotes: 3

lubilis
lubilis

Reputation: 4160

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

Upvotes: 0

Related Questions