Adding values in a column based on conditional statement from another table-CodePudding

I have two tables, table1 and table2. I would like to create table3 where a new column (cole) is created based on conditional statements related to table 1.

When colc is between 0-49, make cole value 0. When colc is between 50-99, make cole value 1 When colc is 100 , make cole value 2

I would normally accomplish this with some kind of for loop in python, but I can only use postgresql and I am struggling to find a way to do what I want (I feel like this is very doable, but I am out of search ideas at this point and don't think I am searching for the right things)

Any help would be appreciated, I have been stuck for hours :(

CodePudding user response：

Use correlated subquery where cola of table1 <= colc of table2

-- PostgreSQL (v11)
SELECT t.colc
     , t.cold
     , (SELECT MAX(colb) FROM table1 WHERE cola <= t.colc) cole
FROM table2 t

please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c730d54cb7efc98e8dd2aabb31957622