SQL Server - Help with SQL query – select table for join based on a condition.

I have 2 tables TableA and TableB that we use for reporting purposes. We join these tables with other tables & select the FundName column from these.

TableA has some funds that are exclusive to TableA only (i.e. they are not present in TableB)

Also, TableB has some funds that are exclusive to TableB only (i.e. they are not present in TableA)

Finally, there are some funds that are common in both TableA as well as TableB.

I need a join query that first looks for the funds in TableA. If the corresponding Fund does not exist in TableA, then we join the earlier tables in the query with TableB.

I think what you are really after is a UNION query not a JOIN.  Here's an example where two tables have different column names.  A UNION requires the same columns in a result set.

select colA as A, colB as B
 from TableA
 where Some set of conditions
UNION ALL
select colC as A, colD as B
 from TableB
 where Some set of conditions
order by some stuff