By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ta (
empid int(10),
sales int(10)
);
INSERT INTO ta VALUES
(101, 9898),
(201, 988),
(301, 87988),
(401, 88),
(501, 202088);
CREATE TABLE tb (
target int(10),
bonus varchar(100)
);
INSERT INTO tb VALUES
(1000, '25%'),
(5000, '50%'),
(10000, '70%'),
(50000, '85%'),
(100000, '100%');
SELECT ta.empid, ta.sales, MAX(tb.target) AS target, CONCAT(MAX(REPLACE(tb.bonus, '%', '') + 0), '%') AS bonus
FROM ta LEFT JOIN tb ON tb.target <= ta.sales
GROUP BY ta.empid, ta.sales
ORDER BY ta.empid
| empid | sales | target | bonus |
|---|---|---|---|
| 101 | 9898 | 5000 | 50% |
| 201 | 988 | null | null |
| 301 | 87988 | 50000 | 85% |
| 401 | 88 | null | null |
| 501 | 202088 | 100000 | 100% |
SELECT ta.empid, ta.sales, MAX(tb.target) AS target, CONCAT(MAX(REPLACE(tb.bonus, '%', '') + 0), '%') AS bonus
FROM ta LEFT JOIN tb ON tb.target <= ta.sales
GROUP BY ta.empid, ta.sales
HAVING MAX(tb.target) IS NOT NULL -- or just using INNER JOIN
ORDER BY ta.empid
| empid | sales | target | bonus |
|---|---|---|---|
| 101 | 9898 | 5000 | 50% |
| 301 | 87988 | 50000 | 85% |
| 501 | 202088 | 100000 | 100% |