Left Join followed by Outer Join (and vice-versa)

[karimeddini_sla]

I am trying to blend data from 2 sources, and which source takes precedence depends on the status. It seems like whichever join is 2nd does actually happen, and I do not understand why. Is this a bug or a subtle intended behavior?

This is a bare-bones example of my script:

foo:
LOAD *
INLINE [key,status_foo,val
1,b,qwerty
2,b,asdf
];

bar:
LOAD *
INLINE [key,status_bar
1,b
2,a
3,b
4,a
];

foobar_q:
OUTER JOIN(foo)
LOAD * RESIDENT bar WHERE status_bar = 'a';

foobar_b:
LEFT JOIN(foo)
LOAD * RESIDENT bar WHERE status_bar = 'b';

DROP TABLE bar;

This is the output I expect:

key	status_foo	status_bar	val
1	b	b	qwerty
2	b	q	asdf
4	-	q	-

[MK_QSL · Accepted solution]

I can't see anything wrong here.

Initially you are outer joining foo with bar for status_bar = a

here you will get output as    

key,        status_foo,     val,          status_bar

1,           b,                   qwerty,     -

2,           b,                   asdf,         a

4,          -                         -            a

Now you are Left joining foo (above resultant table) with bar

so the join will take care wrt key field (key&status_bar) and not only key field as foo table has now status_bar field also

so when you are doing left join you don't have 3 & b so you will not get data for 3 and b !

[karimeddini_sla]

I want "key=1,status_bar=b,status_foo=b" as well. Your mention that status_bar was part of the key for the 2nd join helped me realize how to get that. Thank you for your help.

[MK_QSL]

IF you got your answer, kindly mark appropriate answer.

[#5]

Peter, your left join happending on both key & status_bar that leads to wrong results. Please find the below script.

foo:

LOAD *

INLINE [key,status_foo,val

1,b,qwerty

2,b,asdf

];

bar:

LOAD *

INLINE [key,status_bar

1,b

2,a

3,b

4,a

];

Join (foo)

LOAD key ,  status_bar Resident bar where status_bar = 'a' ;

Left Join (foo)

LOAD key ,  status_bar as temp_bar_b Resident bar where status_bar = 'b' ;

DROP Table bar;

foobar:

LOAD key , val , status_foo , if(IsNull(status_bar) , temp_bar_b , status_bar ) as status_bar Resident foo;

DROP Table foo;

[#6]

Please find the attached qvw.