Conditional Join in DB2

Question

2 tables are given

Table T_PRICE

P_Price    P_Type    P_Route
15         red       long
10                   long
5                    small

Table T_ROUTE

R_Type     R_Route
blue       long
red        long
black      small

I need to display all Routes with prices. When there is no price for type, price should be from table T_PRICE without type.

My approach:

Select * from T_ROUTE
left outer join T_PRICE on P_Route = R_Route 
and case when P_Type <> R_Type then '' else R_Type end = P_Type

Wrong Result:

R_Type R_Route  P_Price P_Type P_Route
blue   long     NULL    NULL   NULL
red    long     15      red    long
black  small    NULL    NULL   NULL

Right result should be:

R_Type R_Route  P_Price P_Type P_Route
blue   long     10             long
red    long     15      red    long
black  small    5              small

How do I get the conditional join to work? Thank you!..

Answer 1

You need two joins here, one with the "matching" type set, another with "default" type.

I'm making two assumptions:

• "Missing" p_type is an empty string, not null, as follows from your query.
• There is at most one row in t_price for each p_route and empty p_type.

:

select r_route, r_type, coalesce(match.p_price, dflt.p_price)
from t_route
left outer join t_price match 
  on (r_route, r_type) = (match.p_route, match.p_type)
left outer join t_price dflt
  on r_route = dflt.p_route and dflt.p_type = ''

dbfiddle link _{(It would be nice if you'd included it yourself).}