CODEWITHSUNDEEP
mysqlsql
Carl McKie
Carl McKie

Reputation: 65

How do I select a field based on the value of another field in SQL?

I have a table which contains the following fields:

current_field
field_1
field_2
field_3
...etc...

current_field contains the name of the field to be selected.

If current_field contained "field_23", how would I construct the select statement to select current_field and field_23?

Upvotes: 0

Views: 4083

Answers (3)

Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

MySQL provides and option of prepared statement and execute it.

If your field current_field contains dynamic columns or if it contains the comma separated value, then you can try something like,

CREATE TABLE fieldDemo (
    current_field VARCHAR(256), 
    field_1 VARCHAR(16), 
    field_2 VARCHAR(16), 
    field_3 VARCHAR(256)
);

INSERT INTO fieldDemo VALUES('field_2,field_3','f1','f2','f3');

SELECT current_field FROM fieldDemo INTO @t;

SET @tm = CONCAT('select ',@t, '  from fieldDemo');

PREPARE stmt FROM @tm

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

MySQL document Reference

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use a giant case expression:

select (case when current_field = 'field_1' then field_1
             when current_field = 'field_2' then field_2
             . . . 
        end) as current_field_value

Note: This requires that all the field values have the same (or compatible) types.

In general, this type of operation suggests that you have an issue with your data model. This is not a typical type of expression.

A better data model would be to store this in two tables, something like:

  • entities table with entity_id, current_field
  • fields table with entity_id, field_name, field_value

Then you could express the logic as:

select e.*, f.*
from entities e join
     fields f
     on e.entity_id = f.entity_id
where f.field_name = e.current_field;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use CASE expression

SELECT current_field
    ,CASE current_field 
       WHEN 'field_1' THEN field_1
       WHEN 'field_2' THEN field_2
       WHEN 'field_3' THEN field_3
     END as current_value
FROM tab

Upvotes: 0

Related Questions