Select Statement With Multiple Rows From Condition On Values In Single Column

I have the table below.Using salary as condition I want to get multiple rows. Below is current table call it employee. empid name salary --------------------------

Solution 1:

Your question is unclear, because your logic implies that you should only have 3 output rows for 3 input rows. Your output however implies that you want to compare the salary to certain fixed values, and every time the salary is larger than the fixed value, show a record in output.

If the former is the case, Minh's query is all you need. In the latter case, you can do something like this:

select e.*, m.incometype
from employee e
left join 
( 
 select0as threshold, 101as incometype
 union
 select5999as threshold, 102as incometype
 union
 select17999as threshold, 103as incometype
) m
on e.salary > m.threshold
orderby e.empid

If you want to add a calculate column i.e. one with values calculated using columns in this query, you can simply add it as a column in the select clause, like so:

select e.*, 
m.incometype, 
casewhen<firstcondition>then<business logic here>
....
else<handle defaultcase>endas yourcomputedcolumn
from
...

Solution 2:

This returns 3 rows and enough for your need:

SELECT empid, name, salary, 
    caseWhen salary<6000then101When salary Between6000And18000Then102Else103Endas incometype
    FROM employee;

Solution 3:

Not very clear on the requirement, however the following worked for me:

Select 
    EmpId,Name,Sal,101 IncomeType
from Emp
UnionallSelect 
    EmpId,Name,Sal,102from Emp
Where Sal >6000unionallSelect 
    EmpId,Name,Sal,103from Emp
Where Sal >18000;