CODEWITHSUNDEEP
sqlsqlite
Saad Khan
Saad Khan

Reputation: 49

How to populate a column in SQL by comparing values from one table against another

I have two tables:

Vacation_days:

id    name     work_days    tier
--------------------------------
 1    John        366       Null
 2    Mike        764       Null
 3    Josh       1163       Null

Vacation_algorithm:

day1     day2        tier
-------------------------
    0        184      1
  185        364      2
  365       5999      3
 6000    1000000      4

I want to iterate through each row in the vacation_days table and see what tier the work_days in the vacation_days corresponds with (work_days must lie in between start_day and end_day).

Then I want to place that tier number in the tier column contained in vacation_days which is currently filled with null values.

I have tried this: 

Select tier 
From Vacation_Algorithm 
Where (Select work_days 
       From vacation_days 
       Where work_days = 366) between day1 and day2.

This returns the correct output, which is a column with tier 3. However, I want a formula to do this with all the rows in my Vacation_days table.

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Just use a join. I would choose a left join, so no rows get left behind if none match:

select vd.*, va.tier
from vacation_days vd left join
     vacation_algorithm va
     on vd.work_days between va.day1 and va.day2;

Upvotes: 2

Related Questions