CODEWITHSUNDEEP
sqloracle-databasejoin
Ganesh
Ganesh

Reputation: 506

SQL Query to find designation of employee

I have 2 tables employee and job_role as below. I need to write a SQL query to find designation of each employee by joining this table.

Input Table

1.Employee

   e_id    e_name  Salary       Commission    
    -------------------------------------
    1       ABC     1000        10
    2       CDE     2000        4
    3       GHI     3500        40
    4       JKL     5000        3
    5       MNO     1200        25
    6       XYZ     3000        2

2.Job_role

Designation   Sal_min   Sal_max   Commission_Min   Commission_Max
-----------------------------------------------------------------
Associate      1000      2000       0                   5
Lead           2001      3000       6                   10
Manager        3001      5000       11                  50

Problem: To find designation of each employee based on below logic using SQL Query

Logic:

if sal between 1000 and 2000 
    AND Commission between  0 and 5 then Associate

if sal between 2001 and 3000 
    OR Commission between  6 and 10 then Lead

if sal between 3001 and 5000 
    OR Commission between  11 and 50 then Manager

Desired output:

e_id    e_name  Salary  Commision  Designation
----------------------------------------------   
 1      ABC     1000       10      Lead
 2      CDE     2000        4      Associate
 3      GHI     3500       40      Manager
 4      JKL     5000        3      Manager
 5      MNO     1200       25      Manager
 6      XYZ     3000        2      Lead

My Attempt: 

select e_id,e_name,salary,commision, 
case when designation='Associate' And commision between Commission_Min   and Commission_Min   
then 'Associate'
else designation end designation
from employee e left outer join job_role
on salary between sal_min and sal_max;

Issue: How to check AND condition(commission) only for Associate not for other?

Upvotes: 1

Views: 5077

Answers (1)

Chintha
Chintha

Reputation: 95

Try This 

select e.*,j.Designation from 
employee e
left outer join
Job_role j
on (j.Designation="Associate" and 
(e.Salary between j.Sal_min and j.Sal_max) and (e.Commission between j.Commission_Min and j.Commission_Max)) 
or(j.Designation not in("Associate") and 
((e.Salary between j.Sal_min and j.Sal_max) or (e.Commission between j.Commission_Min and j.Commission_Max)))
order by e.e_id ;

Upvotes: 1

Related Questions