Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 705
  • Last Modified:

Use value as field name in select query

table_1
id field_name record_id
1 Location        7
2 Location        9
3 Distance       11

table_2
id Location Distance
7    A1          99
9    A2          98
11  A3          97

I need to get the following result
id  field_name value
1  Location     A1
2  Location     A2
3  Distance     97

So to use the field names stored in first table as values in a query.
Is there a way to do it?
Please do not suggest PHP solution.
0
APRESTOUS
Asked:
APRESTOUS
  • 7
  • 5
  • 5
  • +2
1 Solution
 
APRESTOUSAuthor Commented:
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.
0
 
shru_0409Commented:
select id,  field_name,  Location
from table1 inner join table2 on (record_id = id)

try this
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
APRESTOUSAuthor Commented:
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.
0
 
shru_0409Commented:
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...
0
 
SharathData EngineerCommented:
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;

Open in new window

0
 
APRESTOUSAuthor Commented:
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.
0
 
shru_0409Commented:
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?

0
 
APRESTOUSAuthor Commented:
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?

0
 
rooholaCommented:
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

Open in new window

0
 
SharathData EngineerCommented:
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;

Open in new window

0
 
SharathData EngineerCommented:
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;

Open in new window

0
 
shru_0409Commented:
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
0
 
APRESTOUSAuthor Commented:
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
0
 
shru_0409Commented:
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..
0
 
SharathData EngineerCommented:
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;

Open in new window

0
 
APRESTOUSAuthor Commented:
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
0
 
SharathData EngineerCommented:
I am not sure how are you executing. Please see the attached.
mysql> create table temp_table_2(col_1 varchar(10),col_2 varchar(10),id int);
Query OK, 0 rows affected (0.48 sec)

mysql> 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');
Query OK, 0 rows affected (0.48 sec)

mysql> set @sql = replace(@sql,',',' as col_2, id as id from table_2 union all '
);
Query OK, 0 rows affected (0.01 sec)

mysql> set @sql = concat(@sql,' as col_2, id as id from table_2');
Query OK, 0 rows affected (0.00 sec)

mysql> set @sql = replace(@sql,':',', ');
Query OK, 0 rows affected (0.00 sec)

mysql> set @sql = replace(@sql,'#',', ');
Query OK, 0 rows affected (0.00 sec)

mysql> set @sql = concat('insert into temp_table_2 ',@sql);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare s1 from @sql;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute s1;
Query OK, 6 rows affected (0.16 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> deallocate prepare s1;
Query OK, 0 rows affected (0.00 sec)

mysql> 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;
+------+------------+-------+
| id   | field_name | value |
+------+------------+-------+
|    1 | Location   | A1    |
|    2 | Location   | A2    |
|    3 | Distance   | 97    |
+------+------------+-------+
3 rows in set (0.01 sec)

mysql> drop table temp_table_2;
Query OK, 0 rows affected (0.12 sec)

Open in new window

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now