SQL LEFT JOIN WITH SPLIT

Question

I want to do a left join on a table where the format of the two columns are not the same. I use REPLACE to remove the "[ ]" but I'm having trouble making one of the rows into two rows so be able to complete the join.

emp_tbl                        state_tbl
emp       state                id      name
+--------+-------+             +------+-----+
| Steve  | [1]   |             | 1    | AL  |
| Greg   | [2|3] |             | 2    | NV  |
| Steve  | [4]   |             | 3    | AZ  |
+--------+-------+             | 4    | NH  |
                               +------+-----+


Desired output:
+--------+------+
| Steve  | AL   |
| Greg   | NV   |
| Greg   | AZ   |
| Steve  | NH   |
+--------+------+

SELECT emp_tbl.emp, state_tbl.name
FROM emp_tbl
LEFT JOIN state_tbl on state_tbl.id = REPLACE(REPLACE(emp_tbl.state, '[', ''), ']', '')

With this query i can remove the "[ ]" and do the join, but the row with two "states" does obiously not work.

You can [Split] the data Using Reference from another question asked [here]. [Split]: https://stackoverflow.com/questions/13527537/sql-query-to-split-column-data-into-rows [here]: https://stackoverflow.com/questions/13527537/sql-query-to-split-column-data-into-rows — Mani, Nov 09 '21 at 08:47

Eric Lin · Accepted Answer · 2021-11-09T08:52:17.393

Your query will never produce 4 rows because the left table only has 3 rows. You need to flatten the rows that contains multiple state_ids before the join.

Prepare the table and data:

create or replace table emp_tbl (emp varchar, state string);
create or replace table state_tbl (id varchar, name varchar);

insert into emp_tbl values
  ('Steve', '[1]'), ('Greg', '[2|3]'), ('Steve', '[4]');

insert into state_tbl values
  (1, 'AL'), (2, 'NV'), (3, 'AZ'), (4, 'NH');

Then below query should give you the data you want:

with emp_tbl_tmp as (
    select emp, parse_json(replace(state, '|', ',')) as states from emp_tbl
), 
flattened_tbl as (
    select emp, value as state_id from emp_tbl_tmp, table(flatten(input => states))
)
select emp, name from flattened_tbl emp
left join state_tbl state on (emp.state_id = state.id);

Or if you want to save one step:

with flattened_emp_tbl as (
    select emp, value as state_id 
    from emp_tbl, 
    table(flatten(
        input => parse_json(replace(state, '|', ','))
    ))
)
select emp, name from flattened_emp_tbl emp
left join state_tbl state 
  on (emp.state_id = state.id);

eshirvana · Answer 2 · 2021-11-09T16:53:50.270

0

here is how you can do it :

select emp_tbl.emp, state_tbl.name
from emp_tbl tw
lateral flatten (input=>split(parse_json(tw.state), '|')) s
left join state_tbl on s.value = state_tbl.id

edited Nov 09 '21 at 16:53

answered Nov 09 '21 at 16:48

eshirvana

23,227
3
22
38

SQL LEFT JOIN WITH SPLIT

2 Answers2