CODEWITHSUNDEEP
sql
Anand
Anand

Reputation: 1

Can I query a table where it’s name is stored in a column

I have the following tables

Table 1 

ID  Table_Name  Product
--  ----------  ------------
1   Sample_1    PRD1
1   Sample_2    PRD2
1   Sample_4    PRD4
2   Sample_3    PRD3

Basically column TableName contains actual Table Name that needs to be queried

Sample_1 (Table Name)

Dept_no    
-------    
123
456
789

Sample_2 (Table Name)

Dept_no
-------
125
457

Sample_3 (Table Name)

Dept_no
-------
025
0157
0010
12456

Sample_4 (Table Name)

Dept_no
-------
12345
457
79000
98345

Desired Output (Into a New table)

Table 2 

ID  Dept_no     Product
--  ----------  ------------
1   123          PRD1
1   456          PRD1
1   789          PRD1
1   125          PRD2
1   457          PRD2
1   12345        PRD4
1   457          PRD4
1   79000        PRD4
1   98345        PRD4
2   025          PRD3 
2   0157         PRD3 
2   0010         PRD3
2   12456        PRD3

Can you please let me know if we can query a table name residing in a column.

Upvotes: 0

Views: 56

Answers (1)

Felix.leg
Felix.leg

Reputation: 749

There is a solution for that problem, but on condition that you know all possible table names which you want to reference in the Table 1:

For example if there was only Table1, Sample_1 and Sample_2 (added newlines for readability):

select l.Dept_no, Table1.product from 
    (
       select "Sample_1" as name, Dept_no from Sample_1 
       union 
       select "Sample_2" as name, Dept_no from Sample_2
    ) as l 
    inner join Table1 on l.name = Table1.Table_Name
;

I tested it on SQLLite so some commands should be written differently depending of what SQL you use. But all you need is to first UNION all the tables your Table1 may reference as one sub-query, then JOIN that sub-query with Table1.

Upvotes: 1

Related Questions