Table a (milestone) :

Table b :

My problem is I want to select the floor date row of Table A.but I don't know how to use the command.

The method is, in each row of Table B. Determine the column of ins_date and emp_id. Then use that value to select the date in Table A that is the floor date (I don't know how to explain it. But try to understand in the example below). and then show the results in the list.

for example

Row 1: Table B. The ins_date value is '2022-01-20' and emp_id is 2. Consider emp_id = 2 and the floor date of '2022-01-20' (Because Greater than 2022-01-20 ), so select the item PK_ID = 1.

Row 2: Table B. The ins_date value is '2019-03-30' and emp_id is 2. Consider emp_id = 2 and the floor date of '2018-06-29' (Because Greater than 2018-06-29 but less than 2021-03-23 ), so select item PK_ID = 3.

Row 3: Table B. The ins_date value is '2017-06-29' and emp_id is 3. Consider emp_id = 3 and the floor date of '2016-02-17' (Because Greater than 2016-02-17 but less than 2018-01-15 ), so select the item PK_ID = 5.

Thank you and Sorry my English.

P.S. I don't know how I can explain the question and details. But if you edit the text to make it easier to understand. I strongly allow editing.

CodePudding user response：

You are looking for the maximum milestone_date that is less or equal to an ins_date.

Two ways to get that date:

option #1

select b.*, max(a.milestone_date) as floor_milestone_date
from b
join a on a.emp_id = b.emp_id and a.milestone_date <= b.ins_date
group by b.pk_id
order by b.pk_id;

option #2

select 
  b.*, 
  (
    select max(a.milestone_date)
    from a
    where a.emp_id = b.emp_id and a.milestone_date <= b.ins_date
  ) as floor_milestone_date
from b;

The difference between the two queries above: When there is no floor milestone date for an ins_date, then the first query doesn't return the b row, while the second query returns the b row with a null milestone date.

Now, if you want more information from table a, then join the table to one of the queries above.

The final query

select ab.*, a.pk_id
from
(
  select b.*, max(a.milestone_date) as floor_milestone_date
  from b
  join a on a.emp_id = b.emp_id and a.milestone_date <= b.ins_date
  group by b.pk_id
) ab
join a on a.emp_id = ab.emp_id and a.milestone_date = ab.floor_milestone_date
order by ab.pk_id;