where exits between two tables

[rafael5958]

Hi, I have to compare two tables and set an column (yes/no) if the values are the same in a tirdh. Example:

table A:
x

table B:
a
s
d

table C:
a
s
d
status yes/no where a contains x

drop table b;

-------------------------------------------------

TABLE A

x

yy

gg

oo

çç

ee

ww

qq

zz

xx

cc

vv

bb

TABLE B
a	s	d
ff	ff	ff
gg	gg	gg
uu	uu	uu

TABLE C
a	s	d	status
ff	ff	ff	no
gg	gg	gg	yes
uu	uu	uu	no

drop table b;

[Saravanan_Desingh · Accepted solution]

Try this,

TableA:
LOAD * INLINE [
    x
    yy
    gg
    oo
    çç
    ee
    ww
    qq
    zz
    xx
    cc
    vv
    bb
];

TableB:
LOAD *, If(Exists(x,a),'yes','no') As status;
LOAD * INLINE [
    a, s, d
    ff, ff, ff
    gg, gg, gg
    uu, uu, uu
];

Drop Field x;

[Saravanan_Desingh]

Output:

[QFabian]

Hi @rafael5958 , pease check if this works for you, the key here is the function EXISTS :

A:
Load
    x
FROM YOURSOURCE;

B:
Load
    a,
    s,
    d
    'Yes' as Status
FROM YOURSOURCE
where

    exists(x, a);

B:
Load
    a,
    s,
    d
    'No' as Status
FROM YOURSOURCE
where

     not exists(x, a);