Sql join when column is available else join with remaining columns?

Question

I want to left join table T1 with T2 on columns a,b,c .

I first try to join on all the three columns, but they don't join, so now as a fallback I join on columns a,b.

How do I implement this?

example

T1 :

+---+---+---+-------+
| a | b | c | mycol |
+---+---+---+-------+
| 1 | 2 | 3 | john  |
| 1 | 3 | 4 | cena  |
+---+---+---+-------+

T2:

+---+---+---+-------+
| a | b | c | value |
+---+---+---+-------+
| 1 | 2 | 3 |    10 |
| 1 | 3 |   |    20 |
+---+---+---+-------+

after join:

+---+---+---+-------+-------+
| a | b | c | mycol | value |
+---+---+---+-------+-------+
| 1 | 2 | 3 | john  |    10 |
| 1 | 3 | 4 | cena  |    20 |
+---+---+---+-------+-------+

Answer 1

Presumably, you want two left joins and conditional logic

select t1.*, coalesce(t20.a, t21.a) a2, coalesce(t20.b, t21.b) b2, coalesce(t20.c, t21.c) c2
from t1
left join t2 t20 
    on t20.a = t1.a  and t20.b = t1.b and t20.c = t1.c
left join t2 t21
    on t20.a is null and t21.b = t1.b and t21.c = t1.c

The first left join attempts to match on the three columns. The second one joins on the first two columns only, and only when the first join came back empty. You can then use coalesce() in the select clause to bring the relevant value.