How to get table name from another table in select query

Transact-SQL
1

In my sql in i am trying to get table name from another table in SELECT query but it seems to be not working,

NOTE: declaring variables in not allowed in the platform where i am writing this SQL

I have tried this but no luck

`SELECT * from (select de_name from ENT.DISTINCT_DE_TABLE)  WHERE email = 'abc@yahoo.com'`

I have tried this as well, still no luck

 With TableVariable As (
       SELECT DE_NAME FROM DISTINCT_DE_TABLE
       )
    
    SELECT * FROM from mytable where mytable = (SELECT DE_NAME FROM With TableVariable) and email = 'abc@yahoo.com'

another approach - no luck

SELECT x.* 
FROM
 (
    SELECT de_name 
    FROM ENT.DISTINCT_DE_TABLE
 ) AS x
WHERE email = 'abc@yahoo.com'

Any advise on how to go about it?

NOTE: email field does not exist in ENT.DISTINCT_DE_TABLE, it exists in the table that will be pulled from ENT.DISTINCT_DE_TABLE as DE_NAME.

2

You would need dynamic SQL to do this - which requires using a variable to construct the query to be executed and passing that string to EXECUTE or sp_ExecuteSql.

If you need some type of macro substitution - then you are going to need to figure out how to do that in the platform you are using, or you need to create a stored procedure and execute the procedure. Either way - you need to be very careful with this approach and make sure you are fully aware of SQL injection issues and build your code appropriately.

3

hi @jeffw8713 thank you for your response.

yes i am exploring that solution to construct a sql that dynamically builds a sql query. SQL procedures are not supported in the platform.

I was hoping if there is any workaround by simply using sql which would be an ideal solution without making it too complex.

any ideas what can i try within sql without procedures?

4

Could something like:

SELECT m.*
FROM mytable m 
JOIN ENT.DISTINCT_DE_TABLE dt
ON m.mytablename = dt.DE_NAME
WHERE m.email = 'abc@yahoo.com';

I assume SELECT DE_NAME FROM DISTINCT_DE_TABLE returns one or more values.
Another thought if email is a column in table DISTINCT_DE_TABLE

SELECT DE_NAME FROM DISTINCT_DE_TABLE WHERE email - 'abc@yahoo.com';
5

There is nothing in SQL that allows for object substitution - to do that you need to use dynamic SQL or your tool needs to construct the query.

1 Like
6

Hi @djj55 Adding table schemas here for more clarity on my question


- DISTINCT_DE_TABLE scheme (this table only holds the audience list names, nothing else):

FieldName Data Type
DE_Name text



- Records in DISTINCT_DE_TABLE look like this:

DE_NAME
NewsletterEmail_audienceDE_Table
CouponEmail_audienceDE_Table
OrderEmail_audienceDE_Table


- Example of audience TABLE schema (picking any random TABLE):

FieldName Data Type
email emailaddress
c_id int
name text


- Sample data in audience TABLE

email c_id name
john@abc.com 101 John Lee
mike@yahoo.com 102 Mike H
Lee@gmail.com 103 Lee Jr