I have two tables: items and orders
items
--------------
id (int) | type_1 (int) | type_2 (int)|
orders
--------------
id (int) | transaction_type enum ('type_1', 'type_2')
Basically, I want to do the following:
select (select transaction_type from orders where id=1) from items;
So, the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name.
You may want to see the answer to this question, which I believe is what you're trying to accomplish. In short, the answer suggests using prepared statements in order to simulate an eval()-esque functionality. In your case, this may work (you can see the SQLFiddle here:
SELECT transaction_type FROM orders WHERE id=1 into @colname;
SET @table = 'items';
SET @query = CONCAT('SELECT ',@colname,' FROM ', @table);
PREPARE stmt FROM @query;
EXECUTE stmt;
I won't claim to be any sort of expert on the underlying mechanics at work, but per the comments it seems to achieve the goal. Again, this was adopted from another answer, so if it works makes sure to +1 that one :)
the problem is that string returned by select transaction_type from orders where id=1, cannot be converted into column name
You have to PIVOT the values of the column transaction_type of the orders table like so:
SELECT
id,
MAX(CASE WHEN transaction_type = 'type_1' THEN 1 END) Type1,
MAX(CASE WHEN transaction_tyep = 'type_2' THEN 2 END) type2
FROM orders
GROUP BY id
Then you can JOIN the two tables like so:
SELECT i.id - what do you want to select
FROM items i
INNER JOIN
(
SELECT
id,
MAX(CASE WHEN transaction_type = 'type_1' THEN 1 END) Type1,
MAX(CASE WHEN transaction_tyep = 'type_2' THEN 2 END) type2
FROM orders
GROUP BY id
) o ON i.type_1 = o.type1 AND i.type_2 = o.type2 -- you can add more conditions
