CODEWITHSUNDEEP
sql
Md. Mahmudul Hasan
Md. Mahmudul Hasan

Reputation: 51

SQL join logic to take the availabe matched values and common values for unmatched ones

may be my problem is bit weird. Usually we join two tables with the condition of matched or unmatched values of columns. But, here I need all the matching values if available as well as the common value of 'other' if the values not matched.

I have two tables like following. One is 'Employee' table. Another one is 'Increment' table. If the 'Dept' and 'Rank' of Employee table are found in Increment table I will take the increment of that value. But if 'Dept' is available and 'Rank' is unavailable at Increment table I would take the value of that 'Dept' and rank specified by 'other'. If both are unavailable at Increment table it should take the value specified by 'other' for both.
They consist data like following one.

    EmployeeId. Name    Dept.  Rank Salary
        1       Jack    Sales  1   1000
        2       Robi    Sales  2   1200
        3       Martin  Sales  3   1700
        4       Chris   HR     1   1000 
        5       Angel   HR     2   1400
        6       Luke    Acc    1   1300

     Dept.  Rank  Increment
     Sales   1     50
     Sales  Other  70
     HR      1     60
     HR     Other  75
     Other  Other  65

I want to have output like the following one:

    EmployeeId. Name    Dept. Rank Salary Increment
        1       Jack    Sales  1   1000    50
        2       Robi    Sales  2   1200    70
        3       Martin  Sales  3   1700    70 
        4       Chris   HR     1   1000    60
        5       Angel   HR     2   1400    75
        6       Luke    Acc    1   1300    65

Upvotes: 2

Views: 64

Answers (1)

peterm
peterm

Reputation: 92785

Try it this way

SELECT e.employeeid, e.name, e.dept, e.rank, e.salary,
       COALESCE(i.increment, o.increment, oo.increment) increment
  FROM employee e JOIN increment oo
    ON oo.dept = 'Other'
   AND oo.rank = 'Other' LEFT JOIN increment i
    ON e.dept = i.dept
   AND e.rank = i.rank
   AND i.rank <> 'Other' LEFT JOIN increment o
    ON e.dept = o.dept
   AND o.rank = 'Other'

Output:

| EMPLOYEEID |   NAME |  DEPT | RANK | SALARY | INCREMENT |
|------------|--------|-------|------|--------|-----------|
|          1 |   Jack | Sales |    1 |   1000 |        50 |
|          2 |   Robi | Sales |    2 |   1200 |        70 |
|          3 | Martin | Sales |    3 |   1700 |        70 |
|          4 |  Chris |    HR |    1 |   1000 |        60 |
|          5 |  Angel |    HR |    2 |   1400 |        75 |
|          6 |   Luke |   Acc |    1 |   1300 |        65 |

Here is SQLFiddle demo

Upvotes: 2

Related Questions