Join table where table name is a value in another table

Question

I have been tasked with getting some data out of another database not designed by me, so the design cannot be altered.

Looking at the data I need to join a table dynamically based on the value held in another table.

E.G

SELECT * FROM TableA LEFT JOIN TableB oN TableA.TBID = TableB.ID LEFT JOIN TableB.TableOffset AS C ON C.TableAID = TableA.ID

So table B has a column called TableOffset, this holds the name of the table that needs to be joined on as C

(I've tried to do an SQLFIDDLE but the site isn't working with SQL Server ATM)

The issue is there are 112 attribute tables where the data could be, so doing a left join for every one of them would slow down the query I imagine.

So based on the below I would need to get a result set of:

| TableAID | TableATitle | AttrTableA21 | AttrTableA22 |
|----------|-------------|--------------|--------------|
|1         |test         | Name         | 2019         |
|2         |test2        | Name 2       | 2016         |

Example SQL Code

CREATE TABLE [dbo].[TableA](
  [ID] [int] NOT NULL,
  [Title] [nvarchar](100) NOT NULL,
  [TableBID] [int] NOT NULL
  );
CREATE TABLE [dbo].[TableB](
  [ID] [int] NOT NULL,
  [TableName] [nvarchar](100) NOT NULL
  );
CREATE TABLE [dbo].[ATTR_A](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_B](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_C](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_D](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );  
  INSERT INTO TableA VALUES(1, 'test', 1);
  INSERT INTO TableB VALUES(1, 'ATTR_C');
  INSERT INTO ATTR_C VALUES (1, 1, 'Name', 2019);
  INSERT INTO TableA VALUES(2, 'test2', 2);
  INSERT INTO TableB VALUES (2, 'ATTR_A');
  INSERT INTO ATTR_A VALUES (1, 2, 'Name 2', 2016);
    ```

Answer 1

You were told already, that this approach is the wrong one entirely. But if you have to stick with this, you might try this:

Your test scenario in a new database (carefull, if you use this database name already...)

USE master;
GO
CREATE DATABASE MyTestDb;
GO
USE MyTestDb;
GO
CREATE TABLE [dbo].[TableA](
  [ID] [int] NOT NULL,
  [Title] [nvarchar](100) NOT NULL,
  [TableBID] [int] NOT NULL
  );
CREATE TABLE [dbo].[TableB](
  [ID] [int] NOT NULL,
  [TableName] [nvarchar](100) NOT NULL
  );
CREATE TABLE [dbo].[ATTR_A](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_B](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_C](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );
CREATE TABLE [dbo].[ATTR_D](
  [ID] [int] NOT NULL,
  [TableAID] [int] NOT NULL,
  [A21] [nvarchar](100) NOT NULL,
  [A22] [int] NOT NULL
  );  
  INSERT INTO TableA VALUES(1, 'test', 1);
  INSERT INTO TableB VALUES(1, 'ATTR_C');
  INSERT INTO ATTR_C VALUES (1, 1, 'Name', 2019);
  INSERT INTO TableA VALUES(2, 'test2', 2);
  INSERT INTO TableB VALUES (2, 'ATTR_A');
  INSERT INTO ATTR_A VALUES (1, 2, 'Name 2', 2016);
GO

--I create an inlined table valued function where all attribute tables are returned as one set using UNION ALL
--The engine is smart enough, to avoid the call, if the parameter does not fit.

CREATE FUNCTION dbo.GetTheRightSet(@SetKey VARCHAR(10))
RETURNS TABLE
AS
RETURN
    SELECT ID,TableAID,A21,A22 FROM dbo.ATTR_A WHERE @SetKey='ATTR_A'
    UNION ALL
    SELECT ID,TableAID,A21,A22 FROM dbo.ATTR_B WHERE @SetKey='ATTR_B'
    UNION ALL
    SELECT ID,TableAID,A21,A22 FROM dbo.ATTR_C WHERE @SetKey='ATTR_C'
    UNION ALL
    SELECT ID,TableAID,A21,A22 FROM dbo.ATTR_D WHERE @SetKey='ATTR_D'
GO

--This is how to use it

SELECT TableA.*
      ,TableB.*
      ,TheSet.* 
FROM TableA 
LEFT JOIN TableB ON TableA.TableBID = TableB.ID 
OUTER APPLY dbo.GetTheRightSet(TableB.TableName) TheSet
GO

--Clean up (carefull with real data)

USE master;
GO
DROP DATABASE MyTestDb;