How to join to tables whose names are stored as values in another table?

Question

Ι have some tables ( eg. [Table1], [Table2], [Table3] and so on ) with a [ID] as primary key and a RecTime as DATETIME on each.

Αlso Ι have a table [Files] that hold files in a varbinary(max) column, and refers to the other tables having their Names and IDs.

[Table2], [Table3] and others have different structure, but share the [ID] and [RecTime] column exactly as in [Table1]

Below is a quick sample to visualize data.

DECLARE @Table1 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)
DECLARE @Table2 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)
DECLARE @Table3 as table (
      [ID] [bigint]
    , [RecTime] [datetime]
)

DECLARE @Files as table (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    /* and some other columns */
)

INSERT INTO @Table1 (
      [ID]
    , [RecTime]
)
          SELECT '1', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '2', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '3', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '4', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '5', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Table2 (
      [ID]
    , [RecTime]
)
          SELECT '11', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '12', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '13', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '14', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '15', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Table3 (
      [ID]
    , [RecTime]
)
          SELECT '21', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '22', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '23', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '24', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '25', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO @Files (
      [ID]
    , [tblName]
    , [tblID]
    , [BinaryData]
)
          SELECT '1', 'Table1', '1', 0x010203040506
UNION ALL SELECT '2', 'Table1', '2', 0x010203040506
UNION ALL SELECT '3', 'Table1', '2', 0x010203040506
UNION ALL SELECT '4', 'Table1', '3', 0x010203040506
UNION ALL SELECT '5', 'Table1', '4', 0x010203040506
UNION ALL SELECT '6', 'Table1', '5', 0x010203040506
UNION ALL SELECT '7', 'Table1', '5', 0x010203040506

UNION ALL SELECT '8', 'Table2', '11', 0x010203040506
UNION ALL SELECT '9', 'Table2', '11', 0x010203040506
UNION ALL SELECT '10', 'Table2', '12', 0x010203040506
UNION ALL SELECT '11', 'Table2', '13', 0x010203040506
UNION ALL SELECT '12', 'Table2', '14', 0x010203040506
UNION ALL SELECT '13', 'Table2', '12', 0x010203040506
UNION ALL SELECT '14', 'Table2', '15', 0x010203040506

UNION ALL SELECT '15', 'Table3', '21', 0x010203040506
UNION ALL SELECT '16', 'Table3', '22', 0x010203040506
UNION ALL SELECT '17', 'Table3', '24', 0x010203040506
UNION ALL SELECT '18', 'Table3', '23', 0x010203040506
UNION ALL SELECT '19', 'Table3', '25', 0x010203040506
UNION ALL SELECT '20', 'Table3', '25', 0x010203040506
UNION ALL SELECT '21', 'Table3', '21', 0x010203040506

SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT * FROM @Table3

SELECT * FROM @Files

How can I join [Files] table to other tables, the Name and ID of which derive from a value in '[Files]' table ?

I need [BinaryData] from [Files] table and [RecTime] from respective table reference in [Files] table.

The real problem is that [Table1], [Table2] and [Table3] are not the only tables that are referred [Files] table. New tables can be created, for which binary data must be stored in [Files] table.

So I'm looking for a way to "join" them dynamically.

P.S. I'm not the creator of this system, and can not perform any structural change on it, but just trying to solve this problem.

Any help would be appreciated.

Warsle

select tblName,BinaryData,RecTime from files f join table1 t on f.tblID = t.id where tblName = 'Table1' union all select tblName,BinaryData,RecTime from files f join table2 t on f.tblID = t.id where tblName = 'Table2' union all select tblName,BinaryData,RecTime from files f join table3 t on f.tblID = t.id where tblName = 'Table3' you need replace with table names in your example – Immature

Did you try dynamic query and executed it at the end? – Dipnoan

I think that your problem is one of schema design. Clearly there is a relationship between entities that is not represented (properly). Does the solution have to use the schema/data as described, or is a solution that uses an alternate database design that achieves the same intention OK? – Tennes

Your Example is based on 3 Tables, in reality how many tables can there be (i.e over 100)? – Gerrald

Hi please check this link #8852301 answered by @Harun Prasad – Leon

#8852301 already solved here. – Leon

Answer 1

This is the simplest way to do the above. No need of looping or any thing. You need dynamic code as Tables can be add at any time.

Note: In your sample data for Files table seems have wrong data in tblId ?

So I am changing your data to match IDs to respective tables.

Schema:

CREATE TABLE Table1   (
      [ID] [bigint]
    , [RecTime] [datetime]
)
CREATE TABLE Table2 (
      [ID] [bigint]
    , [RecTime] [datetime]
)
CREATE TABLE Table3 (
      [ID] [bigint]
    , [RecTime] [datetime]
)

CREATE TABLE Files (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    /* and some other columns */
)

INSERT INTO Table1 (
      [ID]
    , [RecTime]
)
          SELECT '1', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '2', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '3', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '4', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '5', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Table2 (
      [ID]
    , [RecTime]
)
          SELECT '11', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '12', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '13', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '14', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '15', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Table3 (
      [ID]
    , [RecTime]
)
          SELECT '21', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '22', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '23', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '24', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)
UNION ALL SELECT '25', DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0)

INSERT INTO Files (
      [ID]
    , [tblName]
    , [tblID]
    , [BinaryData]
)
          SELECT '1', 'Table1', '1', 0x010203040506
UNION ALL SELECT '2', 'Table1', '2', 0x010203040506
UNION ALL SELECT '3', 'Table1', '2', 0x010203040506
UNION ALL SELECT '4', 'Table1', '3', 0x010203040506
UNION ALL SELECT '5', 'Table1', '4', 0x010203040506
UNION ALL SELECT '6', 'Table1', '5', 0x010203040506
UNION ALL SELECT '7', 'Table1', '5', 0x010203040506

UNION ALL SELECT '8', 'Table2', '11', 0x010203040506
UNION ALL SELECT '9', 'Table2', '11', 0x010203040506
UNION ALL SELECT '10', 'Table2', '12', 0x010203040506
UNION ALL SELECT '11', 'Table2', '13', 0x010203040506
UNION ALL SELECT '12', 'Table2', '14', 0x010203040506
UNION ALL SELECT '13', 'Table2', '12', 0x010203040506
UNION ALL SELECT '14', 'Table2', '15', 0x010203040506

UNION ALL SELECT '15', 'Table3', '21', 0x010203040506
UNION ALL SELECT '16', 'Table3', '22', 0x010203040506
UNION ALL SELECT '17', 'Table3', '24', 0x010203040506
UNION ALL SELECT '18', 'Table3', '23', 0x010203040506
UNION ALL SELECT '19', 'Table3', '25', 0x010203040506
UNION ALL SELECT '20', 'Table3', '25', 0x010203040506
UNION ALL SELECT '21', 'Table3', '21', 0x010203040506

Now your Dynamic Query Part:

DECLARE @QRY VARCHAR(MAX)='', @Tables VARCHAR(MAX)='';

--Capturing List of Table names for selecting RecTime
SELECT @Tables = @Tables+ tblName+'.RecTime,' FROM (
SELECT DISTINCT tblName FROM Files
)A

--To remove last comma
SELECT @Tables = SUBSTRING(@Tables,1, LEN(@Tables)-1)

--Preparing Dynamic Qry
SELECT @QRY = '
SELECT Files.ID,Files.BinaryData
,COALESCE('+@Tables+') AS RecTime
FROM Files '

SELECT @QRY =@QRY+ JOINS FROM (
SELECT  DISTINCT '
LEFT JOIN '+ tblName + ' ON Files.tblID = '+tblName+'.ID AND Files.tblName= '''+tblName+''''
as JOINS
FROM Files
)A

print @QRY

EXEC( @QRY)

If you want to see what @Qry contains

/*
Print Output:

SELECT Files.ID,Files.BinaryData
,COALESCE(Table1.RecTime,Table2.RecTime,Table3.RecTime) AS RecTime
FROM Files 
LEFT JOIN Table1 ON Files.tblID = Table1.ID AND Files.tblName= 'Table1'
LEFT JOIN Table2 ON Files.tblID = Table2.ID AND Files.tblName= 'Table2'
LEFT JOIN Table3 ON Files.tblID = Table3.ID AND Files.tblName= 'Table3'

*/

Boman

brilliant ! thank you for mentioning about my mistake about wrong data. I'll correct it for future use. – Warsle

Answer 2

One approach is to create a cte that will contain all the tables data (of course, using dynamic sql to create it), and then select from files left join that cte.

This way, the dynamic sql is quite simple to write and maintain, and also the sql it statement it produces is very simple:

DECLARE @SQL varchar(max) = ''
SELECT @SQL = @SQL +' UNION ALL SELECT ID, 
                                       RecTime, 
                                       '''+ tblName +''' AS TableName 
                                FROM ' + tblName 
FROM (
    SELECT DISTINCT tblName FROM files    
) x
-- replace the first 'UNION ALL' with ';WITH allTables as ('
SELECT @SQL = STUFF(@SQL, 1, 11, ';WITH allTables as (') 
       +') 
       SELECT * 
       FROM Files 
       LEFT JOIN allTables ON(tblName = TableName AND tblId = allTables.Id)'

The sql statemet you get from this is:

;WITH allTables as ( 
    SELECT ID, RecTime, 'Table1' AS TableName 
    FROM Table1 
    UNION ALL  
    SELECT ID, RecTime, 'Table2' AS TableName 
    FROM Table2 
    UNION ALL  
    SELECT ID, RecTime, 'Table3' AS TableName 
    FROM Table3
 ) 
 SELECT * 
 FROM Files 
 LEFT JOIN allTables ON(tblName = TableName AND tblId = allTables.Id)

To execute it:

EXEC(@SQL)

Results:

ID    tblName   tblID   BinaryData  ID    RecTime                 TableName
1     Table1    1       123456      1       31.03.2060 00:00:00   Table1
2     Table1    2       123456      2       03.12.1997 00:00:00   Table1
3     Table1    2       123456      2       03.12.1997 00:00:00   Table1
4     Table1    3       123456      3       02.07.2039 00:00:00   Table1
5     Table1    4       123456      4       17.06.1973 00:00:00   Table1
6     Table1    5       123456      5       06.12.2076 00:00:00   Table1
7     Table1    5       123456      5       06.12.2076 00:00:00   Table1
8     Table2    1       123456      NULL    NULL                    NULL
9     Table2    3       123456      NULL    NULL                    NULL
10    Table2    3       123456      NULL    NULL                    NULL
11    Table2    4       123456      NULL    NULL                    NULL
12    Table2    5       123456      NULL    NULL                    NULL
13    Table2    5       123456      NULL    NULL                    NULL
14    Table2    5       123456      NULL    NULL                    NULL
15    Table3    1       123456      NULL    NULL                    NULL
16    Table3    1       123456      NULL    NULL                    NULL
17    Table3    1       123456      NULL    NULL                    NULL
18    Table3    3       123456      NULL    NULL                    NULL
19    Table3    3       123456      NULL    NULL                    NULL
20    Table3    3       123456      NULL    NULL                    NULL
21    Table3    4       123456      NULL    NULL                    NULL

Live demo on rextester

Kynewulf

Answer 3

One solution is to use a cursor that executes some dynamic SQL for each row in the @Files table:

-- Copy table variables into temporary tables so they can be referenced from dynamic SQL
SELECT * INTO #Table1 FROM @Table1;
SELECT * INTO #Table2 FROM @Table2;
SELECT * INTO #Table3 FROM @Table3;

-- Create a temporary table for storing the results
CREATE TABLE #results (
      [ID] [bigint]
    , [tblName] nvarchar(255) NULL
    , [tblID] bigint NULL
    , [BinaryData]  varbinary(max)
    , [RecTime] [datetime]
);

-- Declare placeholders and cursor
DECLARE @ID bigint;
DECLARE @tblName nvarchar(255);
DECLARE @tblID bigint;
DECLARE @BinaryData varbinary(max);
DECLARE @RecTime datetime;
DECLARE @sql nvarchar(max);
DECLARE @params nvarchar(max);

DECLARE files_cursor CURSOR FOR  
SELECT ID, tblName, tblID, BinaryData
FROM @Files

-- Loop over all rows in the @Files table
OPEN files_cursor   
FETCH NEXT FROM files_cursor INTO @ID, @tblName, @tblID, @BinaryData

WHILE @@FETCH_STATUS = 0   
BEGIN   
   -- Find the referenced table row and extract its RecTime.
   SET @RecTime = NULL;
   SET @sql = CONCAT(
       'SELECT @RecTime = RecTime FROM #', @tblName, ' WHERE ID = ', @tblID);
   SET @params = '@RecTime datetime out';

   EXEC SP_EXECUTESQL @sql, @params, @RecTime out;

   -- Add result
   INSERT INTO #results (ID, tblName, tblID, BinaryData, RecTime)
   VALUES (@ID, @tblName, @tblID, @BinaryData, @RecTime); 

   FETCH NEXT FROM files_cursor INTO @ID, @tblName, @tblID, @BinaryData;
END   

-- Finalise
CLOSE files_cursor;
DEALLOCATE files_cursor;

-- Display the results from temporary table
SELECT * FROM #results;

Online demo: http://rextester.com/DXCK86463

Fitzsimmons

Hi Steve, I have a question, why RecTime field is NULL for Table2 and Table3 in the result of cursor? For instance, in tblName Table2 and tblId 1 RecTime field value should be 12.12.1934 00:00:00 I think so, maybe I'm not getting someting – Fodder

Looking at the table data, the IDs are 1-5 for Table1, 11-15 for Table2 and 21-25 for Table3. But the tblIDs in the Files table are all 1-5, which is why nothing is found for Table2 and Table3. – Fitzsimmons

Some more performance related improvements: - drop the temp tables at the end - if you want to use cursor, use fast_forward read_only cursors - i would create a longer sql text that contains union alls for the result sets and use the cursor only for create one sql statement. And run that statement at the end. In that case there will be only one call instead of one call for each row. Anyway, nice job. – Poindexter

Answer 4

Try the following.

Select res.* , F.* From Files F
Left join
(
Select 'table1' as tablename, a.* From table1 a
Union
Select 'table2' as tablename, b.* From table2 b
Union
Select 'table3' as tablename, c.* From table3 c
)Res
On res.tablename = F.tblname

Reive

As I mentioned, [Table1], [Table2] and [Table3] are not the only tables. New tables can be created, for which binary data must be stored in [Files] table. – Warsle

Make this inline query as a view and then alter the view when there is a new table been added in Files table. Writing dynamic query for this requirement may impact performance. – Reive

I found this useful. Thanks. – Garwin

Answer 5

This design is just a way to model a hierarchy in ER. You basically have a physically partitioned table based on table names (ie Table1, Table2 and so on). As such the easiest way to join these tables is to create a partitioned view and then join it.

In the case of your example you just have to do:

CREATE VIEW vmAll AS
SELECT 'Table1' AS 'tblName', [ID], [RecTime] FROM Table1
UNION ALL
SELECT 'Table2' AS 'tblName', [ID], [RecTime] FROM Table2
UNION ALL
SELECT 'Table3' AS 'tblName', [ID], [RecTime] FROM Table3;
GO

Now just join it with the Files table as usual (remember to specify the partitioning field too):

For example this:

SELECT 
      F.[ID] 
    , F.[tblName]
    , F.[tblID]
    , F.[BinaryData] 
    , A.RecTime
    FROM [Files] F
LEFT OUTER JOIN vmAll A ON
    F.[ID] = A.[ID] AND
    F.tblName = A.tblName

Gives the expected result:

Notice an important thing: since it's a partitioned view SQL Server is able to perform partition elimination thus speeding up the join considerably (the correct term here should be table elimination).

For example the previous execution plan was:

If we add a filter predicate on the partitioning column:

SELECT 
      F.[ID] 
    , F.[tblName]
    , F.[tblID]
    , F.[BinaryData] 
    , A.RecTime
    FROM [Files] F
LEFT OUTER JOIN vmAll A ON
    F.[ID] = A.[ID] AND
    F.tblName = A.tblName

WHERE A.tblName = 'Table1'

We will get this execution plan (notice two tables are not scanned at all):

Of course in order to use the partitioned view you have to be able to create it first. You can do it programmatically looking for the specific fields with a query like this one:

;WITH CTE AS
(
    SELECT C.object_id FROM sys.columns C
    INNER JOIN sys.objects O ON C.object_id = O.object_id
    WHERE 
        (C.[name] = 'ID' OR C.[name] = 'RecTime')
        AND O.[type] = 'U'
    GROUP BY C.object_id
    HAVING COUNT(*) = 2
)
SELECT OBJECT_NAME(object_id), object_id FROM CTE;

Arnitaarno

Answer 6

If you have only few tables, then you can just do this, It could be slightly faster because it avoids Dynamic SQL.

Look at other solutions(I like Steve chamber's solution) here if you can't tell how many tables there will be or if there will be too many.

SELECT F.*, RecTime = 
      CASE tblName  
         WHEN 'Table1' THEN COALESCE(T1.RecTime, NULL)
         WHEN 'Table2' THEN COALESCE(T2.RecTime, NULL)
         WHEN 'Table3' THEN COALESCE(T3.RecTime, NULL)
         ELSE NULL
      END 
FROM @Files F
LEFT JOIN @Table1 T1 ON F.tblID = T1.ID
LEFT JOIN @Table2 T2 ON F.tblID = T2.ID
LEFT JOIN @Table3 T3 ON F.tblID = T3.ID

Demo : http://rextester.com/FWWD90002

Festoon

Answer 7

Have a look on below links. this might solve your problem.

MySQL join tables where table name is a field of another table , MySQL join tables where table name is a field of another table

Weatherbeaten