Join Tables in Snowflake on Specific Criteria

user_19513131 0
Viewed 41

Attempting to join two tables, A and B in Snowflake on specific criteria. I want to join on Person_id but the Person_id row from Table B has to be 1+row from Table A.

Table: A
|Person_id |  Name    |
|----------|----------|
| 0        | John     |
| 1        | Patel    |
| 2        | Aaron    |

Table: B
|Person_id |  Hourly  |
|----------|----------|
| 1        | 20       | 
| 2        | 30       |
| 3        | 25       |

I want Table A to look like this after the join:

Table A: 
|Person_id |  Name    | Hourly |
|----------|----------|--------|
| 0        | John     | 20     |
| 1        | Patel    | 30     |
| 2        | Aaron    | 25     |
snowflake-cloud-data-platform sql
1 Answers

The join condition allows calculations, so just add one to the PERSON_ID for Table A:

create table TABLE_A (PERSON_ID int, NAME string);
create table TABLE_B (PERSON_ID int, HOURLY int);

insert into TABLE_A (PERSON_ID, NAME) values
(0, 'John'),
(1, 'Patel'),
(2, 'Aaron');

insert into TABLE_B (PERSON_ID, HOURLY) values
(1, 20),
(2, 30),
(3, 25);

select A.PERSON_ID, A.NAME, B.HOURLY
from TABLE_A A 
    left join TABLE_B B 
        on A.PERSON_ID + 1 = B.PERSON_ID
;
user_127563810
Related