Condition in sql query with sql join

Question

I have two tables, 'salary' and 'conversion_rate'.

Salary table contain salary of each employee for each month, there will be only one row for each employee for a month. But for all employees month can be same like 2021-Dec salary for all employees. e.g:

name	date	salary
john	2021-12-25	50000
john	2021-11-25	50000
harry	2021-12-25	50000
harry	2021-11-25	50000

Second Table is conversion_rate. it contains salary conversion rates like dollar or euro rate for each month but it is possible that for a given month there is no conversion rate. like for example for 2021-Dec there is no conversion rate available. e.g:

rate	date
170.23	2021-12-25
160.40	2021-11-25
174.44	2021-08-25

Question: I want to join each row in salary table with its conversion rate in conversion_rate table on the basis of date column. but if there is no date available in conversion_rate table then it should pick previous nearest date and its conversion rate?

for Example:

select name, salary, rate 
from salary as s
left join conversion_rate as c on (c.date = s.date || c.date < s.date)

I want to do this in sql query.

Answer 1

The tables are tiny, so you may use

SELECT name, 
       salary, 
       ( SELECT rate
         FROM conversion_rate AS c
         WHERE c.`date` <= s.`date`
         ORDER BY c.`date` DESC LIMIT 1) AS rate 
FROM salary AS s;

The index conversion_rate (`date`) is valuable.