Adding values to table1 column based on dynamic conditions from table2-CodePudding

I have 2 tables, table1 and table2 and I am trying to add a column to table2 based on values a from table1 and some sort of dynamic conditional statement on c from table2 to get e in table3. This has to be dynamic because table1 will change in both row count and values in columns a and b.

I am not sure how to approach this problem and any help would be appreciated.

table1
 a   |  b
----- -----
   0 | 1
   5 | 2
  10 | 3
  20 | 4

table2
 c   |  d
----- -----
  2 | 100  
  3 | 101
  5 | 102
  7 | 103
 11 | 104
 13 | 105
 17 | 106
 19 | 107
 23 | 108

table3
 c   |  d   |   e
----- ------ ------
  2 | 100  | 1
  3 | 101  | 1
  5 | 102  | 2
  7 | 103  | 2
 11 | 104  | 3
 13 | 105  | 3
 17 | 106  | 3
 19 | 107  | 3
 23 | 108  | 4

CodePudding user response：

Basic goal is: For each row in table2, find the row with greatest a in table1 that is less than or equal to table2.c

Try this outer join:

The setup:

CREATE TABLE table1 (a int, b int);
INSERT INTO table1 VALUES 
  (   0 , 1 )
, (   5 , 2 )
, (  10 , 3 )
, (  20 , 4 )
;

CREATE TABLE table2 (c int, d int);

INSERT INTO table2 VALUES
  ( 2 , 100) 
, ( 3 , 101)
, ( 5 , 102)
, ( 7 , 103)
, (11 , 104)
, (13 , 105)
, (17 , 106)
, (19 , 107)
, (23 , 108)
;