Second join based on the lack of success of the first

[Bob Stearns]

I only want to do second join if the first was unsuccessful and was
wondering if something like the following would be valid:

select t1.*, t2.*, t3.*
from t1
left outer join table
(select ta.*, tb.uniquename
from ta
left outer join tb on tb.key=ta.key
where ta.key=t1.key) t2 on 0=0
left outer join table
(select tc.*
from tc
where tc.key=t1.key
and t2.key is null) t3 on 0=0

The real questions are: is the reference to t2.key in t3 valid; and will
it be null if t2 fails to match?

The above query is an extremely simplified form of the real query, which
would be 125-150 lines long in a (semi) readable format.

[Serge Rielau]

Bob Stearns wrote:

I only want to do second join if the first was unsuccessful and was
wondering if something like the following would be valid:

select t1.*, t2.*, t3.*
from t1
left outer join table
(select ta.*, tb.uniquename
from ta
left outer join tb on tb.key=ta.key
where ta.key=t1.key) t2 on 0=0
left outer join table
(select tc.*
from tc
where tc.key=t1.key
and t2.key is null) t3 on 0=0

The real questions are: is the reference to t2.key in t3 valid; and will
it be null if t2 fails to match?

The above query is an extremely simplified form of the real query, which
would be 125-150 lines long in a (semi) readable format.

select t1.*, t2.*, t3.*
from t1
left outer join table
LATERAL(select ta.*, tb.uniquename
from ta
left outer join tb on tb.key=ta.key
where ta.key=t1.key) t2 on 0=0
left outer join table
LATERAL(select tc.*
from tc
where tc.key=t1.key
and t2.key is null) t3 on 0=0

This is lateral correlation, hence you need teh lateral keyword.
If in your version of DB2 LATERAL gives a syntax error, try TABLE instead.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Tonkuma]

You can refere to t2.key in t3.

and will it be null if t2 fails to match?

I think so.

My question:
You wrote "The above query is an extremely simplified ..."
But, can you remove second subselect to simplify the statement?

select t1.*, t2.*, t3.*
from t1
left outer join table
(select ta.*, tb.uniquename
from ta
left outer join tb on tb.key=ta.key
where ta.key=t1.key) t2 on 0=0
left outer join
tc t3
ON t3.key=t1.key
and t2.key is null