Join a table whose name is stored in the first table-CodePudding

I have a first table [TABLE1] with columns [ID], [Description], [DetailTable]. I want to join [TABLE1] with the [DetailTable]. The name of [DetailTable] is stored in [TABLE1] column.

"SELECT * FROM TABLE1 CROSS JOIN ?????"

Any suggestions?

CodePudding user response：

So... if you cheat and SELECT * from the detailtab, you could do something a bit like this, with dynamic SQL:

-- For the example, choose either 1 or 2 to see the #foo table or the #bar table
DECLARE @Id INT = 1

-- EXAMPLE SCENARIO SETUP --
CREATE TABLE #ListOfTables
( ID INT IDENTITY(1,1) NOT NULL 
 ,[Description] NVARCHAR(255) NOT NULL
 ,[DetailTable] NVARCHAR(255) NOT NULL);
CREATE TABLE #foo
(Foothing VARCHAR(20));

CREATE TABLE #bar
(Barthing VARCHAR(20));

-- TEST VALUES --
INSERT #ListOfTables VALUES ('foo','#foo'),('bar','#bar');
INSERT #foo VALUES ('A foothing Foothing');
INSERT #bar VALUES ('A barthing Barthing');


-- THE SCRIPT -- 
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL =
'   SELECT Tab.Id, Tab.[Description], Tab2.*
    FROM #ListOfTables AS Tab
    CROSS JOIN '   T.DetailTable   ' AS Tab2
    WHERE Tab.Id = '   CONVERT(VARCHAR(10),@Id)
FROM #ListOfTables T
WHERE T.Id = @Id;

PRINT @SQL
EXEC sp_executesql @SQL;

-- CLEAN UP --
DROP TABLE #ListOfTables;
DROP TABLE #bar;
DROP TABLE #foo;

However, I have to agree with the comments that this is a pretty nasty way to do things. If you want to choose particular columns and the columns are different for each detail table, then things will start to get really unpleasant... Does this give you something you can start with?

Remember, the best solution will almost certainly involve redesigning things so you don't have to jump through these hoops!

CodePudding user response：

All of the detail tables must have identical schema.

Create a view that unions all the tables

CREATE VIEW vAllDetails AS
SELECT 'DETAIL1' table_name, * from DETAIL1
UNION ALL
SELECT 'DETAIL2' table_name, * from DETAIL2
UNION ALL
SELECT 'DETAIL3' table_name, * from DETAIL3

When you join against this view, SQL Server can generate a plan that uses a "startup predicate expression". For example, a plan like this: sample plan. At first glance, it looks like SQL is going to scan all of the detail tables, but it won't. The left most filters include a "startup predicate", so for each row we read from table1, only if TableName matches will that branch be executed.