Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

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

Tags:

Ι 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.

like image 828
armen Avatar asked Feb 08 '17 09:02

armen

People also ask

How do you join two tables based on conditions?

You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.

How do you join two similar tables?

To join two tables based on a column match without loosing any of the data from the left table, you would use a LEFT OUTER JOIN. Left outer joins are used when you want to get all the values from one table but only the records that match the left table from the right table.

What are the 4 different table joining types?

There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.

Can we join two columns with different names?

Note the following when using UNION in SQL: All SELECT statements should list the same number of columns. The corresponding columns must have the same data type. The corresponding columns can have different names, as they do in our example.

4 Answers

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

like image 80
Zohar Peled Avatar answered Nov 26 '22 12:11

Zohar Peled

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

like image 28
Steve Chambers Avatar answered Nov 26 '22 12:11

Steve Chambers

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'

*/
like image 44
Shakeer Mirza Avatar answered Nov 26 '22 12:11

Shakeer Mirza

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
like image 36
Kalyan Avatar answered Nov 26 '22 10:11

Kalyan
Sign in to Comment

Related questions

Is GraphQL an ORM?
Multipart HTTP response
Using GraphQL Fragment on multiple types
What is the integrity property inside yarn.lock file?
What does the 'At sign' mean in yaml config for azure pipeline
C# and Arrow Keys
ARM vs Thumb performance on iPhone 3GS, non floating point code
Python, PyTables, Java - tying all together
Support for schema migrations with JPA?
Sorting a combobox purely in XAML
Explanation of HashMap#hash(int) method
Migrate from Oracle to MySQL

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!