How can I join one table or other depending on a field value?

Question

I have one table and I'd like to decide which table join depending on the value of a field. I have the table T and there is a field Type, so if Type=1 then I have to join with Table2 and if Type=2 I have to join with Ta

Answer 1

Short answer, you can't. Maybe you can do something like:

select ...
from t
left join t1
    on t.type = 1
    and t.x = t1.x
left join t2
    on t.type = 2
    and t.x = t1.x

to return something (must be same type) from either t1 or t2 you can use:

select coalesce(t1.y, t2.y) ...

Answer 2

Create a Type field in a Union query between Ta and Table2 and then join your T table to the Union table. This method assumes that you can union the results of Ta and Table2.

Select T.*, UnionTable.*
From T
Inner Join
   (Select 1 AS Type, Table2.FieldA As F1, Table2.FieldB AS F2....etc.
    From Table2
       Union ALL
    Select 2 AS Type, Ta.Field1 AS F1, Ta.Field2 As F2....etc.
    From Ta) AS UnionTable
ON T.Type = UnionTable.Type
AND T.miscFieldNameHere = UnionTable.F1
AND etc. etc.

Of course the size of your tables will matter in this type of join.