mysql : table1 field value as name for table2

Question

i have a db like this:

Table1:
----------
id
id_item
tablename (enum: 'table2','table3','table4')

table2:
----------
id
value

table3:
-----------
id
value

table4: [...]

And i want a query similar like this:

SELECT t1.id, t2.value FROM table1 AS t1
LEFT JOIN t1.tablename as t2 ON t1.id_item=t2.id

i tried this "ERROR 1146 (42S02): Table 't1.table' doesn't exist"

please any one suggest a query similar to this or new formate i am reday to use

thanks in advance.

`table` is a keyword in mysql so do not use it as column name or escape it with backticks. But i do not think that this is the solution. — Jens, Mar 04 '15 at 06:18
what you want to do ? Where you want self join ? here you tring to perform join on column of the table which is causing an error — Nagendra Nigade, Mar 04 '15 at 06:21
possible duplicate of [MySQL > Table doesn't exist. But it does (or it should)](http://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should) — Naveen Kumar Alone, Mar 04 '15 at 06:24
its sample query i posted here. i have user table having information of consumer,retailer,agent like this based on user type i need to get the data from corresponding table.user type is exactly same as table name — Praveen, Mar 04 '15 at 06:24
i strongly believe there is no need for different table for consumer,retailer,agent. Have this table structure : first table with column `id ,id_item , type(consumer,retailer,agent)` second table with column `id as foreign key ,value,type(consumer,retailer,agent)` Query `select t1.id , t2.value from firsttable t1 left join secondtable t2 where t1.id=t2.id and t1.type=t2.type` — Nagendra Nigade, Mar 04 '15 at 07:07

Naveen Kumar Alone · Answer 1 · 2015-03-04T06:34:40.587

0

SELECT t1.id, t2.value FROM table1 AS t1 LEFT JOIN t1.tablename as t2 ON t1.id_item=t2.id

Why you are join a table name tablename with first table alias?

t1.tablename

Modify the query as

SELECT t1.id, t2.value FROM table1 AS t1 LEFT JOIN tablename as t2 ON t1.id_item=t2.id
                                                   ^Here

edited Mar 04 '15 at 06:34

answered Mar 04 '15 at 06:19

@Praveen http://anothermysqldba.blogspot.in/2013/09/error-1146-42s02-table-doesnt-exist.html – Naveen Kumar Alone Mar 04 '15 at 06:27
its sample query i posted here. i have user table having information of consumer,retailer,agent like this based on user type i need to get the data from corresponding table.user type is exactly same as table name – Praveen Mar 04 '15 at 06:27
my problem is value for the **t1.tablename** is treating as table name, but i need ** value t1.tablename (eg. table2 ) ** is not taking as table name – Praveen Mar 04 '15 at 06:32
@Praveen k, your table1 contains a columns with name tablename and its value is table2. im right? – Naveen Kumar Alone Mar 04 '15 at 06:36
yes exactly **table1 contains a columns with name tablename and its value is table2** – Praveen Mar 04 '15 at 06:49

Ankit Bajpai · Answer 2 · 2015-03-04T06:50:25.117

0

Try this query:-

SELECT t1.id, t2.value 
FROM table1 t1 
LEFT JOIN (SELECT id, tablename FROM table1) t2
ON t1.id_item=t2.id;

Might this is what you have looking for.

edited Mar 04 '15 at 06:50

answered Mar 04 '15 at 06:30

Ankit Bajpai

in my case table 2 is dynamic some time table 3 i need every thing based on table name field in table1 – Praveen Mar 04 '15 at 06:47

2 Answers2