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

177
August 07, 2021, at 3:40 PM

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.

Rent Charter Buses Company
READ ALSO
Spring Kafka Event after all listeners are started

Spring Kafka Event after all listeners are started

I'm using Spring-Kakfa to connect kakfa cluster and I have a requirement to execute a piece of code after all the topic partitions of all the topics are assignedtake the following code for example

206
missing ic_affirmation_foreground.xml for android developer tutorial

missing ic_affirmation_foreground.xml for android developer tutorial

I'm trying to complete a codelab where I polish the UI on an affirmations app and it tells me to download the files ic_affirmation_foregroundxml and ic_affirmation_background

198
Required field form by using id attribute in Javascript

Required field form by using id attribute in Javascript

here is my formWhen user want to add another form they just click the "tambah bil"

128
Looking for differences in two CSV files with Python

Looking for differences in two CSV files with Python

I have a script that scrapes a site and puts specific site names into a csvSome days it has 0 site names and some days it has more the 4

197