Как мы можем получить значение столбца для использования в качестве имени таблицы для внутреннего соединения?

Попробуйте это

 declare @tabName varchar(1000)
 set @tabName = (select top 1 ProductName as tabName from products)

 declare @query varchar(8000)
 set @query = ' select
 p.ProductName as '''+@tabName+''' 
 from Products p'

---print(@query)
 exec (@query)

В SQL нет стандартного способа сделать это. Подумайте, содержит ли ваша таблица t2 1000 строк, и каждая строка имеет отдельное значение nextOfKinTable. Это приведет к тому, что запрос превратится в соединение таблиц 1002. Не красиво. Я даже не знаю ни одного проприетарного синтаксиса, который поддерживал бы его в известных мне продуктах.

Если количество отдельных значений столбца невелико, вы можете использовать LEFT JOIN, но каждая объединенная таблица получит другой псевдоним (пример с использованием 3 таблиц):

SELECT
   t1.*, --TODO: List columns
   t2.*, --TODO: List columns
   COALESCE(t3.ColumnA,t4.ColumnA,t5.ColumnA) as ColumnA,
   COALESCE(t3.ColumnB,t4.ColumnB,t5.ColumnB) as ColumnB
FROM
    term_relationships as t1 
        INNER JOIN
    modules as t2
        ON
           t2.module_id = t1.object_id
        LEFT JOIN
    Table3 as t3
        ON
           t3.module_id = t2.module_id AND
           t2.nextOfKinTable = 'Table3'
        LEFT JOIN
    Table4 as t4
        ON
           t4.module_id = t2.module_id AND
           t2.nextOfKinTable = 'Table4'
        LEFT JOIN
    Table5 as t5
        ON
           t5.module_id = t2.module_id AND
           t2.nextOfKinTable = 'Table5'

Вы также можете подумать, должны ли эти отдельные таблицы быть одной таблицей с дополнительными столбцами, чтобы различать строки. Эту проблему иногда называют разделением атрибутов. (См. пример разделения таблицы Джо Селко в статье 2009 года)