How to reuse values from multiple columns in the select statement of mysql

Question

I have two tables. Table_1 has three foreign keys that are coming from the table_2. I need to select all rows from table_2 where ids are equal to the values of val_1, val_2 and val_3 of a specific row from table_1. For example:

SELECT val_1, val_2, val_3 from table_1 WHERE id = 1;

And then using the result of the first query run

SELECT name FROM table_2 WHERE id IN (101, 102, 103);

Is there a way to do it in one query?

table_1

id	name	val_1	val_2	val_3
1	item1	101	102	103
2	item2	104	105	106

table_2

id	name
101	sub_item1
102	sub_item2
103	sub_item3
104	sub_item4
105	sub_item5
106	sub_item6

Answer 1

One way to do it is to concatenate val_1, val_2 and val_3 in a subquery and use FIND_IN_SET():

SELECT name 
FROM table_2 
WHERE FIND_IN_SET(
        id, 
        (SELECT CONCAT_WS(',', val_1, val_2, val_3) FROM table_1 WHERE id = 1)
      );

Or, join the tables:

SELECT t2.name 
FROM table_2 t2 INNER JOIN table_1 t1
ON t2.id IN (t1.val_1, t1.val_2, t1.val_3)
WHERE t1.id = 1;

See the demo.

Answer 2

This is a fairly complex JOIN operation.

SELECT t1.name, 
       t1.val_1, v1.name AS name1,
       t1.val_2, v2.name AS name2,
       t1.val_3, v3.name AS name3
  FROM table_1 t1
  LEFT JOIN table_2 v1 ON t1.val_1 = v1.id
  LEFT JOIN table_2 v2 ON t1.val_2 = v2.id
  LEFT JOIN table_2 v3 ON t1.val_3 = v3.id

And adding to @forpas demo...

But think how much easier your life would be if your table_1 had this normalized layout, with one item per row rather than three.

id  name    val 
1   item1   101 
2   item1   102
3   item1   103
4   item2   104 
5   item2   105
6   item2   106

Then your query would be

SELECT t1.name, t1.val, t2.name AS subname
  FROM table_1 t1
  LEFT JOIN table_2 t2 ON t1.val = t2.id
 WHERE t2.id IN (101, 102, 103) 
 ORDER BY t1.name, t2.name

Fiddle here.