CODEWITHSUNDEEP
mysql
Touchpad
Touchpad

Reputation: 722

Using values from a field in one table as column name for field in another

I have 3 tables like this

http://sqlfiddle.com/#!2/df3e6/10

my wish is to use the name-field from t2 as column name for t3

My end goal is something along the lines of:

---------------------------------
| ID | NAME  | AMOUNT | PRODUCT |
---------------------------------
| 1  | name1 |    1   | potato  |
| 2  | name2 |   20   | fruit   |
---------------------------------

Is this doable or do I have to "sort" it this way after the query?

Upvotes: 0

Views: 59

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

Pivot the results based on name value. 

SELECT 
  `t1`.* 
  , max( case when `t2`.`name` = 'amount' then `t3`.`value` 
              else null 
         end ) as 'Amount'
  , max( case when `t2`.`name` = 'product' then `t3`.`value` 
         else null 
         end ) as 'Product'
FROM `t1`
  LEFT JOIN `t3`
    ON `t1`.`id` = `t3`.`t1_id`
  LEFT JOIN `t2`
    ON `t2`.`id` = `t3`.`t2_id`
  GROUP BY `t1`.`id`

Demo @ MySQL 5.5.32 Fiddle

Upvotes: 1

user3209815
user3209815

Reputation: 377

If I understand your problem correctly, you could use a nested query, something like:

SELECT (SELECT `name` AS `value_type` from `t2` WHERE `value` = "product") FROM `t3`

Upvotes: 0

Related Questions