My employee table has a column for work level containing values like 1A, 1B, 1C, 2A, 2B, & 2C. Employee hierarchy is 2C > 2B > 2A > 1C > 1B > 1A.
Now if I write a query like this:
SELECT *
FROM employee
WHERE work_level >= 1C
Would it return the rows containing only work levels 1C, 2A, 2B, 2C?
If not then how can I?
Yes it will return the desired rows, but do remember to add an inverted comma to the where clause::
SELECT * FROM employee WHERE work_level >= '1C'
Try something like this :
Let us say you have some work_levels and input as defined below :
set @work_level1 = '25C';
set @input_work_level1 = '1C';
set @work_level2 = '2C';
set @input_work_level2 = '15C';
set @work_level3 = '2A';
set @input_work_level3 = '1C';
set @work_level4 = '2C';
set @input_work_level4 = '15B';
Running the following query :
select
( cast( left(@work_level1, length(@work_level1)-1) as unsigned )
>=
cast( left(@input_work_level1, length(@input_work_level1)-1) as unsigned )
)
and
right(@work_level1, 1) <= right(@input_work_level1, 1)
as '''25C'' >= ''1C''',
( cast( left(@work_level2, length(@work_level2)-1) as unsigned )
>=
cast( left(@input_work_level2, length(@input_work_level2)-1) as unsigned )
)
and
right(@work_level2, 1) <= right(@input_work_level2, 1)
as '''2C'' >= ''15C''',
( cast( left(@work_level3, length(@work_level3)-1) as unsigned )
>=
cast( left(@input_work_level3, length(@input_work_level3)-1) as unsigned )
)
and
right(@work_level3, 1) <= right(@input_work_level3, 1)
as '''2A'' >= ''1C''',
( cast( left(@work_level4, length(@work_level4)-1) as unsigned )
>=
cast( left(@input_work_level4, length(@input_work_level4)-1) as unsigned )
)
and
right(@work_level4, 1) <= right(@input_work_level4, 1)
as '''2C'' >= ''15B'''
;
would result :
+---------------+---------------+--------------+---------------+
| '25C' >= '1C' | '2C' >= '15C' | '2A' >= '1C' | '2C' >= '15B' |
+---------------+---------------+--------------+---------------+
| 1 | 0 | 1 | 0 |
+---------------+---------------+--------------+---------------+
