MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH center_wise_test_rates AS (SELECT 2 lab_id, 'Cardiology' category_name, 9 test_id, 'Blood Sug' test_name, 1200 rate

UNION ALL

SELECT 2, 'Cardiology', 10, 'Total Cholesterol', 1500

UNION ALL

SELECT 2, 'Cardiology', 11, 'HDL', 1100

UNION ALL

SELECT 2, 'Cardiology', 12, 'LDL', 750),

test_list AS (SELECT 9 test_id, 'Cardiology' category_name, 'Blood Sug' test_name

UNION ALL

SELECT 10 test_id, 'Cardiology' category_name, 'Total Cholesterol' test_name

UNION ALL

SELECT 11 test_id, 'Cardiology' category_name, 'HDL' test_name

UNION ALL

SELECT 12 test_id, 'Cardiology' category_name, 'LDL' test_name

UNION ALL

SELECT 57 test_id, 'Cardiology' category_name, 'abc' test_name)

SELECT (SELECT lab_id

FROM center_wise_test_rates

WHERE category_name = tl.category_name

LIMIT 1) lab_id

,tl.category_name

,tl.test_id

,tl.test_name

,cwtr.rate

FROM test_list tl

LEFT JOIN center_wise_test_rates cwtr ON tl.test_id = cwtr.test_id

AND tl.category_name = cwtr.category_name

lab_id	category_name	test_id	test_name	rate
2	Cardiology	9	Blood Sug	1200
2	Cardiology	10	Total Cholesterol	1500
2	Cardiology	11	HDL	1100
2	Cardiology	12	LDL	750
2	Cardiology	57	abc	null