Use value as field name in select query

http://forums.devarticles.com/mysql-development-50/list-mysql-field-names-and-values-2715.html

It does not answer my qustion.
I need not list but use the value  from one table as field name in other in order to get values from the second table.
In one query.

select id,  field_name,  Location
from table1 inner join table2 on (record_id = id)

try this

Probably my question was not clear.
I can't use Location in query because in the third record of example I need not Location but  Distance.
And of course my example is simplified. In real life there are much more fields and not only Location and Distance as in example.

select id,  field_name,  case when field_name = 'Location' then table_2.location else  table_2.Distance end as value
from table1 inner join table2 on (record_id = id)

y want like this...

you can also try like this.

select t1.id,t1.field_name,if(t1.field_name = 'Location',t2.Location,t2.Distance) value
  from table_1 as t1
 inner join table_2 as t2
    on t1.record_id = t2.id;

Select allOpen in new window

Last version of shru_0409 is the same as the version of Sharath_123
only one uses CASE WHEN THEN and other IF()
It works indeed.
But with many fields it will be big bunch of nested operators.
Not elegant and not universal.
We are not using value as field name. And that was the core of my question.
But i there is no universal solution I will live with it.

please clear the some doubt....
as u said "We are not using value as field name "
1. is it user type argument "field name"?
2. is it output of  fetching the data from the query?

In the table_1
in field name we store the field name of the second table.
Some kind of location

id field_name record_id
1 Location        7

is intended to be used for query that in human language would be
"Show me value situated in table2 in field with name = "Location", record id = 7"

But how to translate it to (My)SQL?

try this:

select table_1.id as id,table_1.field_name as field_name ,if( STRCMP(table_1.field_name,'Location' ), table_2.distance , table_2.location ) as value
from table_1,table_2
where table_1.record_id=table_2.id

Select allOpen in new window

run this query.

create table temp_table_2(col_1 varchar(10),col_2 varchar(10),id int);
set @sql = (
select group_concat(concat(concat(concat('select ''',column_name),''' as col_1: '),column_name)) 
  from information_schema.columns where table_name = 'table_2' and column_name <> 'id');
set @sql = replace(@sql,',',' as col_2, id as id from table_2 union all ');
set @sql = concat(@sql,' as col_2, id as id from table_2');
set @sql = replace(@sql,':',', ');
set @sql = replace(@sql,'#',', ');
set @sql = concat('insert into temp_table_2 ',@sql);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
select t1.id,t1.field_name,t2.col_2 
  from table_1 t1
  join temp_table_2 t2
    on t1.record_id = t2.id and t1.field_name = t2.col_1;
drop table temp_table_2;

Select allOpen in new window

added column alias.

create temporary table temp_table_2(col_1 varchar(10),col_2 varchar(10),id int);
set @sql = (
select group_concat(concat(concat(concat('select ''',column_name),''' as col_1: '),column_name)) 
  from information_schema.columns where table_name = 'table_2' and column_name <> 'id');
set @sql = replace(@sql,',',' as col_2, id as id from table_2 union all ');
set @sql = concat(@sql,' as col_2, id as id from table_2');
set @sql = replace(@sql,':',', ');
set @sql = replace(@sql,'#',', ');
set @sql = concat('insert into temp_table_2 ',@sql);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
select t1.id,t1.field_name,t2.col_2 as value
  from table_1 t1
  join temp_table_2 t2
    on t1.record_id = t2.id and t1.field_name = t2.col_1;
drop table temp_table_2;

Select allOpen in new window

SELECT ID, (select field_name from table1 where record_id = ID) loc,
         table_2.distance
  FROM table2
 
  or  
 
select id, loc ,case when loc = 'Location' then loc else  Distance end as value,  
from (  SELECT ID, (select field_name from table1 where record_id = ID) loc,
         table_2.distance
  FROM table2 )


you want like this

To Sharath_123:

EXPLAIN select t1.id,t1.field_name,t2.col_2 as value
  from table_1 t1
  join temp_table_2 t2
    on t1.record_id = t2.id and t1.field_name = t2.col_1


Table 'test.temp_table_2' doesn't exist

temp_table_2 -- may be he understand the temporary table which is u insert the data in temporary table ...
and he make the join with main table..

try this query. Removed the TEMPORARY keyword in the temporary table creation. That may be causing error.

I assume that your table names as table_1 and table_2. I have created one temporary table as temp_table_2 based on table_2.
You can JOIN table_1 with this temporary table to get your required result with mentioning the column names in the CASE or IF statement. In fact, you need not to use CASE statement in this.
After the result is displayed, I have dropped the temp table.

With my code, you need not to worry about  many no. of columns (Location,Distance etc) in table_2. It will take care of all those columns.

create table temp_table_2(col_1 varchar(10),col_2 varchar(10),id int);
set @sql = (
select group_concat(concat(concat(concat('select ''',column_name),''' as col_1: '),column_name)) 
  from information_schema.columns where table_name = 'table_2' and column_name <> 'id');
set @sql = replace(@sql,',',' as col_2, id as id from table_2 union all ');
set @sql = concat(@sql,' as col_2, id as id from table_2');
set @sql = replace(@sql,':',', ');
set @sql = replace(@sql,'#',', ');
set @sql = concat('insert into temp_table_2 ',@sql);
prepare s1 from @sql;
execute s1;
deallocate prepare s1;
select t1.id,t1.field_name,t2.col_2 as value
  from table_1 t1
  join temp_table_2 t2
    on t1.record_id = t2.id and t1.field_name = t2.col_1;
drop table temp_table_2;

Select allOpen in new window

I got just the same

EXPLAIN select t1.id,t1.field_name,t2.col_2 as value
  from table_1 t1
  join temp_table_2 t2
    on t1.record_id = t2.id and t1.field_name = t2.col_1

Error number 1146

Table 'test.temp_table_2' doesn't exist

I did not get it working. But I tested it only with SQLyog.
But the idea looks me correct.