How to select a list as a virtual column in MySql?

Question

I know I can select a single value using

SELECT 'value' AS col;

But I need return a list of numbers as a column. Something like

SELECT ('value1', 'value2', 'value3') AS col;

More precisely I need to do

SELECT * FROM my_table
INNER JOIN (SELECT ('value1', 'value2', 'value3') AS col ) AS x ON my_table.id = x.col

Does someone know how can I do it?

****EDIT****

Let me explain my problem with more details, maybe you can suggest a different method to solve it.

I have the following table

+--------+--------+------+
| tb1_id | tb2_id | main |
+--------+--------+------+
|     15 |  81123 |    1 |
|     16 |  81599 |    1 |
|     17 |  82101 |    0 |
|     17 |  82102 |    1 |
|     18 |  82101 |    0 |
|     18 |  82102 |    0 |
|     18 |  82103 |    1 |
+--------+--------+------+

I need to build a query that return something like this:

+--------+--------+---------------+
| tb1_id | main   | assoc         |
+--------+--------+---------------+
| 15     | 81123  |               |
| 16     | 81599  |               |
| 17     | 82102  |  82101        |
| 18     | 82103  |  82102, 82101 |
+--------+--------+---------------+

Currently I'm using a subquery to get the result, but I'm having performance issues.

There is no such thing as a list data type for a column in MySQL. Could you possibly mean "set"? — Gordon Linoff, Jul 16 '15 at 14:15
I don't think so. I need to make a query using a IN statement and return the value that matched in result. — Alan Alves de Oliveira, Jul 16 '15 at 14:47
So, `my_table.id` is a text field which has 3 numbers separated by a `,` ? — Vatev, Jul 16 '15 at 14:54
You want to match any of the 3 columns in the inner select with `my_table.id` ? — Vatev, Jul 16 '15 at 15:02
Hi Vatev, I've add more details about my problem in main topic. Do you have some suggestion? — Alan Alves de Oliveira, Jul 16 '15 at 15:22
This sounds like job for GROUP_CONCAT : https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/ — rkosegi, Jul 16 '15 at 15:30

How to select a list as a virtual column in MySql?

0 Answers0