topic Re: Left Join within the same table in App Development

Left Join within the same table

annick — Sat, 16 Nov 2024 04:20:55 GMT

Hello,

I have a table with 3 columns : A,B,C.

Example:

A	B	C
101	X	1
102	Y	1
101		2
102		2

For each line with column C = 2, I would like to retrieve the line (in the same table) with the same value in column A and with column C = 1; Once the match is found, the value of Column B should be copied

Example above would result in:

A	B	C
101	X	1
102	Y	1
101	X	2
102	Y	2

As I have a big volume of data, I would like an optimized way to do this operation and appreciate your advice.

Many thanks in advance.

Annick

Re: Left Join within the same table

dplr-rn — Thu, 17 Oct 2019 19:23:27 GMT

not sure if there are any fruther subtleties involved in your requriement but below approach should work for you

first load values where column b is not empty in a map

i.e.

BColMap:

mapping load

A,B from xyz where len(B)>0;

A	B
101	X
102	Y

then while loading your actual table

load

,if(len(B)>0,B,applymap('BColMap',A,'')) as B

from xyz;